CODEWITHSUNDEEP
postgresqlstored-procedurespsycopg2
benhsu
benhsu

Reputation: 5546

Using Psycopg2 to call a Postgres procedure that returns a record

I have a Postgres procedure whose return type is SETOF record. The procedure takes several parameters, including one parameter which is a list and two which are datetimes. I would like to call this procedure from psycopg2. I am getting this error message:

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

We would like the columns returned from the stored proc to be dynamic (based on user input), so we don't want to use out parameters, RETURN TABLE, RETURN SETOF , or anything else that requires declaring the return type at procedure definition time. The only way to make the return columns dynamic was this:

https://stackoverflow.com/a/6085167/75857

What is the best way of using psycopg to call a stored proc that returns a set of records? We came up with this code which seems to work, but not sure if its recommended:

cursor.execute("SELECT * FROM get_fnord(%s, %s, %s) as (foo integer, bar integer)", ([12561], start, end));

Upvotes: 1

Views: 3804

Answers (2)

VictR
VictR

Reputation: 11

I wanted to call postgres function that returns cursor.
Here is the code:

con = psycopg2.connect("connection string")
cur = con.cursor()
cur.execute("BEGIN")
cur.execute("SELECT * FROM **SP NAME WITH PARAMETER**;")
cur.execute("FETCH ALL from records;") // records is the cursor defined in function   
items = cur.fetchall()

Upvotes: 1

khampson
khampson

Reputation: 15306

I think your solution is fine. It matches the style of what the Postgres doc shows for using a function in the FROM clause of a SELECT statement.

Their example shows it being called both with and without a column definition list:

CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors(111);
 did |    name
-----+-------------
 111 | Walt Disney

CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
    SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;

SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
 f1  |     f2
-----+-------------
 111 | Walt Disney

It would appear that psycopg2 (or perhaps the underlying libpq it wraps) needs the latter form, with the definition.

Doc link: http://www.postgresql.org/docs/9.3/static/sql-select.html

Upvotes: 1

Related Questions