Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to use RETURN TABLE in Postgres 8.4

How to use RETURN TABLE in Postgres 8.4

From
Michal Szymanski
Date:
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

Re: How to use RETURN TABLE in Postgres 8.4

From
Tom Lane
Date:
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

Re: How to use RETURN TABLE in Postgres 8.4

From
Pavel Stehule
Date:
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


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

Re: How to use RETURN TABLE in Postgres 8.4

From
Michael Black
Date:
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.stehule@gmail.com
> To: tgl@sss.pgh.pa.us
> CC: dyrex@poczta.onet.pl; pgsql-general@postgresql.org
>
> 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
>
>
> >                        regards, tom lane
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: How to use RETURN TABLE in Postgres 8.4

From
Michal Szymanski
Date:
> 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#