CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Matt
Matt

Reputation: 1038

How do I express a function that returns a setof record in sqlalchemy?

I need to execute a pg function that returns a setof record type. Here is the function in postgres:

CREATE OR REPLACE FUNCTION testfunction(text, text)
RETURNS SETOF RECORD AS
   'select * from test;'
LANGUAGE sql;

Here is the python:

import sqlalchemy as sa
from sqlalchemy.sql import *

engine = sa.create_engine('postgresql://localhost/test')
conn = engine.connect()
f = func.testfunction('a', 'b')
conn.execute(select([column('a', sa.types.Integer)]).select_from(f))

What I need is to say:

SELECT a FROM testfunction('a', 'b') AS (a integer)

...but I don't know how to express that.

Upvotes: 1

Views: 532

Answers (1)

ch2500
ch2500

Reputation: 240

It appears you can't:

From http://docs.sqlalchemy.org/en/rel_0_9/core/functions.html#sqlalchemy.sql.functions.func

The func construct has only limited support for calling standalone “stored procedures”, especially those with special parameterization concerns.

See the section Calling Stored Procedures for details on how to use the DBAPI-level callproc() method for fully traditional stored procedures.

Obviously, you can fall back to plain SQL interpolation:

>> print sa.select().select_from('a AS f(a int, b int)')
SELECT  
FROM a AS f(a int, b int)

Upvotes: 3

Related Questions