Thread: Returning recordsets with functions

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

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/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING


On Thu, 23 Sep 2004 11:26:15 -0400, Robert Fitzpatrick
<robert@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 majordomo@postgresql.org
>

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

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

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.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

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/guides/SetReturningFunctions
as well as Elein's take on the above on General Bits (I think it was in
#26).