Thread: Re: Can we return a table from a function?

Arundhati wrote:

> Hi
>
> I want the result of select query to be returned from a function. Or
> is there any way to return a table from a function?

What you are looking for is a table function:

http://www.postgresql.org/docs/7.3/interactive/xfunc-tablefunctions.html
http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING


I'm not finding the equivalent for 7.4 about the first link.


Regards
Gaetano Mendola

Perhaps the original question is asking for cursors:

CREATE OR REPLACE FUNCTION
  get_data( refcursor, character varying, character varying, varying)
    RETURNS refcursor AS'
DECLARE
        _arg1_ ALIAS FOR $2;
        _arg2_  ALIAS FOR $3;
        selectstring TEXT;
BEGIN
        selectstring := 'SELECT foo FROM bar
                         WHERE col1=_arg1_ AND col2=_arg2_';
        RAISE NOTICE ''%'', selectstring;
        OPEN $1 FOR EXECUTE selectstring;
        RETURN $1;
END;
' LANGUAGE plpgsql;

More info:
http://www.postgresql.org/docs/7.4/interactive/plpgsql-cursors.html


\<.


On Thu, 2004-09-02 at 14:38, Gaetano Mendola wrote:
> Arundhati wrote:
>
> > Hi
> >
> > I want the result of select query to be returned from a function. Or
> > is there any way to return a table from a function?
>
> What you are looking for is a table function:
>
> http://www.postgresql.org/docs/7.3/interactive/xfunc-tablefunctions.html
> http://www.postgresql.org/docs/7.4/interactive/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING
>
>
> I'm not finding the equivalent for 7.4 about the first link.
>
>
> Regards
> Gaetano Mendola
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match