Using sqlalchemy how invoke a function that return a table

Question

I haven't find a way to return data from a postgres function that return a TABLE:

CREATE OR REPLACE FUNCTION doc_codes(device_id TEXT) RETURNS TABLE("name" TEXT, "suffix" TEXT) AS $$
    SELECT name, prefix_fordevice(name,  device_id) AS pf FROM doccode;
$$ LANGUAGE SQL;

Using:

    f = sq.sql.text("SELECT name, suffix FROM doc_codes(:deviceId)")

    return self.con.execute(f, deviceId=deviceId)

Or

    f = sq.sql.func.doc_codes(deviceId, type_=types.String)

    return self.con.execute(
        select([sq.Column('name', types.String), sq.Column('suffix', types.String)]). \
            select_from(f)
    ).fetchall()

I get the error (ProgrammingError) a column definition list is required for functions returning "record".

Using f = sq.sql.func.doc_code(deviceId, type_=types.String) I get No function matches the given name and argument types. You might need to add explicit type casts and the generated SQL is SELECT doc_code('1') AS doc_code_1, instead of SELECT * FROM.

If a generate a table, I can't pass the deviceId to the table name... now what I can do?

Are you sure you defined your function as RETURNS TABLE (...) not RETURNS SETOF RECORD ? — Craig Ringer
– Craig Ringer, Commented Jan 21, 2014 at 1:39
Damm, that was it. The code in the DB was not up-to-date. However, how do it with SET OF RECORD? — mamcx
– mamcx, Commented Jan 21, 2014 at 2:11

Craig Ringer · Accepted Answer · 2014-01-21 02:14:21Z

3

The function definition must actually be RETURNS SETOF RECORD not RETURNS TABLE to produce that error.

If you actually needed to do it with RETURNS SETOF RECORD, you must call it with a column-definition list, like the error message says:

a column definition list is required for functions returning "record"

See the documentation. You can either use OUT parameters (equivalent to RETURNS TABLE), or call with a col-list, eg:

SELECT name, suffix
FROM doc_codes(:deviceId) AS dc("name" TEXT, "suffix" TEXT)

answered Jan 21, 2014 at 2:14

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using sqlalchemy how invoke a function that return a table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related