Обсуждение: returning rows from a function

You can't return a result set as such from a function. But if you are using
7.2, you can now return a cursor to a result set:

http://www7.us.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-cursors
.html

Greg

----- Original Message -----
From: Nikheel Dhekne
To: pgsql-general@postgresql.org
Sent: Wednesday, February 20, 2002 6:01 PM
Subject: [GENERAL] returning rows from a function


I am new to Postgres and have a rather straightforward question. Is it
possible, and if so, what is the syntax for returning a recordset from a
function?

I have tried

CREATE FUNCTION selectUserProperties(text) RETURNS RECORD

and

CREATE FUNCTION selectUserProperties(text) RETURNS SETOF varchar

Neither of which seems to work. The rest of the function is.

CREATE FUNCTION selectUserProperties (text) RETURNS RECORD
AS '

BEGIN
            SELECT            key,
                                    value
            FROM               user_properties
            WHERE            username = $1;

END;

' LANGUAGE 'plpgsql';

Both key and value in this example are varchar columns.

Thanks for your help.

__________________
Nikheel Dhekne
NetAid | Developer
http://www.netaid.org

Nikheel,

It can done indirectly by returning a refcursor.  I sent out the
attached java program last week on the pgsql-jdbc mail list last week
showing how refcursors can be used to return records.  While recursors
are intended to return the results of a query, you can also use them to
return arbitrary sets of records as well, as the example shows.  It is
ugly but can be done.

thanks,
--Barry


Nikheel Dhekne wrote:
> I am new to Postgres and have a rather straightforward question. Is it
> possible, and if so, what is the syntax for returning a recordset from a
> function?
>
>
>
> I have tried
>
>
>
> CREATE FUNCTION selectUserProperties(text) RETURNS RECORD
>
>
>
> and
>
>
>
> CREATE FUNCTION selectUserProperties(text) RETURNS SETOF varchar
>
>
>
> Neither of which seems to work. The rest of the function is?
>
>
>
> CREATE FUNCTION selectUserProperties (text) RETURNS RECORD
>
> AS '
>
>
>
> BEGIN
>
>             SELECT            key,
>
>                                     value
>
>             FROM               user_properties
>
>             WHERE            username = $1;
>
>
>
> END;
>
>
>
> ' LANGUAGE 'plpgsql';
>
>
>
> Both key and value in this example are varchar columns.
>
>
>
> Thanks for your help.
>
>
>
> __________________
>
> Nikheel Dhekne
>
> NetAid | Developer
>
> http://www.netaid.org
>
>
>
>
>