Thread: returning rows from a function

returning rows from a function

From

"Nikheel Dhekne"

Date:

20 February 2002, 21:11:30

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:

20 February 2002, 22:09:03

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:

21 February 2002, 00:13:58

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

test.java