Returning recordsets with functions

[Robert Fitzpatrick]

Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Kevin Barnard]

Use the return type of SETOF and user the RETURN NEXT for each record.
If you are already returning a record that's half the battle.

http://www.postgresql.org/docs/7.4/s...ENTS-RETURNING
On Thu, 23 Sep 2004 11:26:15 -0400, Robert Fitzpatrick
<ro****@webtent.com> wrote:

Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.

--
Robert

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tim Penhey]

Robert Fitzpatrick wrote:

Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.

Ah ha. Someone else trying to find out what I had hunted for. I could
not find an example on the web, so I started writing one.
Not fully complete yet, but here is what is there and I hope it helps.

http://www.scorefirst.com/postgresql.html

Tim
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Robert Fitzpatrick]

On Thu, 2004-09-23 at 18:28, Tim Penhey wrote:

Robert Fitzpatrick wrote:

Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.

Ah ha. Someone else trying to find out what I had hunted for. I could
not find an example on the web, so I started writing one.
Not fully complete yet, but here is what is there and I hope it helps.
http://www.scorefirst.com/postgresql.html

Thanks to all, that is very helpful. But when I create your function or
a small test function, I get the following error. I am running
PostgreSQL 7.4.5, do you know what this means or how I can define the
list?

ERROR: a column definition list is required for functions returning
"record"

I have another question. I was getting a syntax error when trying to
create the function on the page linked above:

ERROR: syntax error at or near "INTEGER" at character 64

I made my own test function with trying to put my own variable names in
the arguments list and it created without the syntax error, but now back
to the first problem. Here is what I have so far:

CREATE OR REPLACE FUNCTION "public"."test" (varchar) RETURNS SETOF
"pg_catalog"."record" AS'
DECLARE
row_ RECORD;
cursor_ CURSOR FOR SELECT * FROM tblhudreports WHERE rems_id=$1;
BEGIN
OPEN cursor_;
LOOP
FETCH cursor_ INTO row_;
EXIT WHEN NOT FOUND;
RETURN NEXT row_;
END LOOP;

CLOSE cursor_;
RETURN;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

ohc=# select * from test('800000061');
ERROR: a column definition list is required for functions returning
"record"
ohc=#

--
Robert
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Joshua D. Drake]

Hello,

When using setOf functions you first create a type to call from:

|CREATE TYPE desc_results
AS (entry_id INTEGER, headline TEXT, rank REAL);
|
After creating the custom type you create a function that calls that type:

CREATE OR REPLACE FUNCTION dresults(text) RETURNS SETOF desc_results AS '

| SELECT entry_id,headline(description,q), rank(vectors,q)
FROM cmd_contact_task_entries, to_tsquery($1) AS q
WHERE vectors @@ q ORDER BY rank(vectors,q)
DESC;' LANGUAGE 'SQL';

Sincerely,

Joshua D. Drake

|

Robert Fitzpatrick wrote:

On Thu, 2004-09-23 at 18:28, Tim Penhey wrote:

Robert Fitzpatrick wrote:

Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.

Ah ha. Someone else trying to find out what I had hunted for. I could
not find an example on the web, so I started writing one.
Not fully complete yet, but here is what is there and I hope it helps.
http://www.scorefirst.com/postgresql.html

Thanks to all, that is very helpful. But when I create your function or
a small test function, I get the following error. I am running
PostgreSQL 7.4.5, do you know what this means or how I can define the
list?

ERROR: a column definition list is required for functions returning
"record"

I have another question. I was getting a syntax error when trying to
create the function on the page linked above:

ERROR: syntax error at or near "INTEGER" at character 64

I made my own test function with trying to put my own variable names in
the arguments list and it created without the syntax error, but now back
to the first problem. Here is what I have so far:

CREATE OR REPLACE FUNCTION "public"."test" (varchar) RETURNS SETOF
"pg_catalog"."record" AS'
DECLARE
row_ RECORD;
cursor_ CURSOR FOR SELECT * FROM tblhudreports WHERE rems_id=$1;
BEGIN
OPEN cursor_;
LOOP
FETCH cursor_ INTO row_;
EXIT WHEN NOT FOUND;
RETURN NEXT row_;
END LOOP;

CLOSE cursor_;
RETURN;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

ohc=# select * from test('800000061');
ERROR: a column definition list is required for functions returning
"record"
ohc=#

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
[phone] - [e-mail] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

[Stephan Szabo]

On Thu, 23 Sep 2004, Robert Fitzpatrick wrote:

Can someone point me to some more information or perhaps show an example
of returning a recordset from a plpgsql function. I'd like to send an
argument or arguments to the function, do some queries to return a set
of records. I've done several functions that return one value of one
type, but nothing that returns a set.

There's my set returning functions document on techdocs.postgresql.org,
http://techdocs.postgresql.org/guide...rningFunctions
as well as Elein's take on the above on General Bits (I think it was in
#26).

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend