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
