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?

Answer 1

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)