How to use RETURN TABLE in Postgres 8.4

Michal Szymanski <dyrex@poczta.onet.pl> writes:

CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8,
test VARCHAR)
AS $$
BEGIN
-- @todo hide password
RETURN QUERY
SELECT id ,test
FROM bug_table
;
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;

Don't use column names in your functions that are the same as variable
or parameter names of the function. This is working basically as if
you'd written "SELECT null,null", because the output parameters are
still null when the RETURN QUERY is executed.

regards, tom lane

2009/7/3 Tom Lane <tgl@sss.pgh.pa.us>:

Michal Szymanski <dyrex@poczta.onet.pl> writes:

CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8,
test VARCHAR)
    AS $$
BEGIN
    -- @todo hide password
    RETURN QUERY
        SELECT id  ,test
        FROM bug_table
    ;
END;
$$
    LANGUAGE plpgsql STRICT SECURITY DEFINER;

Don't use column names in your functions that are the same as variable
or parameter names of the function.  This is working basically as if
you'd written "SELECT null,null", because the output parameters are
still null when the RETURN QUERY is executed.

use qualified names instead

RETURN QUERY
SELECT b.id, b.test
FROM bug_table b;

regards
Pavel Stehule

Actually, since pgsql does not rely on the names but rather the position of the columns returned to fill the returned table, it would be better to use something like

CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (rv_id INT8,
rv_test VARCHAR)
AS $$
BEGIN
-- @todo hide password
RETURN QUERY
SELECT id as t_id, test as t_test
FROM bug_table
;
END;

Unless you code that calls this function has the column names coded with in it, you can also access the data returned using an index, or position, to get the values in the returned recordset. lv_id = rs.column(1) *if not a zero based language*.

Michal Szymanski <dy...@poczta.onet.pl> writes:

CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8,
test VARCHAR)
    AS $$
BEGIN
    -- @todo hide password
    RETURN QUERY
        SELECT id  ,test
        FROM bug_table
    ;
END;
$$
    LANGUAGE plpgsql STRICT SECURITY DEFINER;

Don't use column names in your functions that are the same as variable
or parameter names of the function.  This is working basically as if

Thank you, now it works. Using RETURNS TABLE will resolve my other
problem related to the bug/functionality of Postgres -
http://groups.google.pl/group/pgsql.bugs/browse_thread/thread/0647bde500c1b782?hl=pl#