PL/PGSQL function, having trouble accessing a returned result set from psycopg2...

Question

I have this pl/pgsql function:

CREATE OR REPLACE FUNCTION get_result(id integer) RETURNS SETOF my_table AS $
    DECLARE
        result_set my_table%ROWTYPE;
    BEGIN
        IF id=0 THEN
            SELECT INTO result_set my_table_id, my_table_value FROM my_table;
        ELSE
            SELECT INTO result_set my_table_id, my_table_value FROM my_table WHERE my_table_id=id;
        END IF;
        RETURN;
    END;
$ LANGUAGE plpgsql;

I am trying to use this with Python's psycopg2 library. Here is the python code:

import psycopg2 as pg
conn = pg.connect(host='myhost', database='mydatabase', user='user', password='passwd')
cur = conn.cursor()
return cur.execute("SELECT * FROM get_result(0);") # returns NoneType

However, if i just do the regular query, I get the correct set of rows back:

...
return cur.execute("SELECT my_table_id, my_table_value FROM mytable;") # returns iterable result set

Theres obviously something wrong with my pl/pgsql function, but I can't seem to get it right.

I also tried using

RETURN result_set;

instead of just

RETURN

in the 10th line of my plpgsql function, but got an error from postgres.

Answer 1

Please read the error you're getting when you change "RETURN;" to "RETURN result_set;". And then read the following - "38.6.1.2. RETURN NEXT and RETURN QUERY".

A hint - in your case you need "RETURN QUERY".