How to use RETURN TABLE in Postgres 8.4

[Michal Szymanski]

I'written something like this:

CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
test VARCHAR,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;

INSERT INTO bug_table (id,test) VALUES (1,'test');
select * from bug_table;

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;
SELECT * FROM buggy_procedure();

---------------------------
it returns 1 but empty row. What is wrong with this?

Regards
Michal Szymanski
http://blog.szymanskich.net

[Tom Lane]

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
you'd written "SELECT null,null", because the output parameters are
still null when the RETURN QUERY is executed.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Pavel Stehule]

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

> 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
> 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

[Michael Black]

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*.



> Date: Fri, 3 Jul 2009 17:49:42 +0200
> Subject: Re: [GENERAL] How to use RETURN TABLE in Postgres 8.4
> From: pavel....@gmail.com
> To: t...@sss.pgh.pa.us
> CC: dy...@poczta.onet.pl; pgsql-...@postgresql.org

[Michal Szymanski]

> 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#