Thread: returning rows from a function

returning rows from a function

From
"Nikheel Dhekne"
Date:

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

 

 

Re: returning rows from a function

From
"Gregory Wood"
Date:
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




Re: returning rows from a function

From
Barry Lind
Date:
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
>
>
>
>
>


Attachment