Re: returning rows from a function

From: Barry Lind <barry(at)xythos(dot)com>
To: Nikheel Dhekne <ndhekne(at)netaid(dot)org>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: returning rows from a function
Date: 2002-02-21 01:02:13
Message-ID: 3C744715.1040508@xythos.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 Content-Type Size
test.java java/* 1.6 KB

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Hugo Coolens 2002-02-21 02:25:28 batch file
Previous Message David Blood 2002-02-21 00:57:08 query optimization Anyone know a better way to run this query