Returning a table

[newbiepg]

How can I return a table using functions in Postgresql 7.2
I need to return a table or part of a table sometimes

 

[rycamor]

What you want is to return a SET, which can consist of all the data in a table. I think you will need PostgreSQL 7.3 to do this, though.

You can "kind of" approximate this behavior by using a cursor in a function definition (here's an example from one of my functions):

CREATE FUNCTION events4prospect(refcursor, int4) RETURNS refcursor AS '
BEGIN

OPEN $1 FOR SELECT to_char(event_date, ''MM/DD/YYYY HH12:MI:SS AM'') 
AS event_date,event_type,event_result FROM prospect_events
WHERE prospect_id = $2;
RETURN $1;

END;
'  LANGUAGE 'plpgsql';

Here, I am returning a set of events for a particular prospect in a tracking system. So, to call this, I have to give the cursor a name, and then call the function, as "SELECT events4prospect('cursor_name', 5);", and this would create a set of results for prospect 5, and pass them to cursor 'curson_name'. Then I would retrieve these rows from the cursor using the query "FETCH ALL in cursor_name;" -------------------------------------------

My PostgreSQL FAQ --

http://brainscraps.com/faq/pg_my.html

 

[newbiepg]

Rycamor
Thanks
I am stuck with 7.22 for now
I will try to see what I can do with the cursor
my manager thinks cursors slow down the speed
maybe I will try to go for an update if possible