Re: How to use RETURN TABLE in Postgres 8.4

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(dot)stehule(at)gmail(dot)com
> To: tgl(at)sss(dot)pgh(dot)pa(dot)us
> CC: dyrex(at)poczta(dot)onet(dot)pl; pgsql-general(at)postgresql(dot)org
>
> 2009/7/3 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> > Michal Szymanski <dyrex(at)poczta(dot)onet(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general