Re: Returning recordsets with functions

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(at)commandprompt(dot)com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL