Re: PLPGSQL SETOF functions - Mailing list pgsql-general

From Sim Zacks
Subject Re: PLPGSQL SETOF functions
Date
Msg-id 4E0B3695.4060403@compulab.co.il
Whole thread Raw
In response to PLPGSQL SETOF functions  (David Greco <David_Greco@harte-hanks.com>)
List pgsql-general

Please reply to the list in the future.

I don't believe you can do that.


Sim

On 06/29/2011 04:39 PM, David Greco wrote:

Thanks that works pretty well. Is it possible to fetch the all the return of dates_pkg.getbusinessdays() into a single variable at once? i.e. in Oracle I would do something like

 

CRATE table_type as TABLE of TYPE record_type;

 

declare

    allrows table_type;

BEGIN

    allrows := dates_pkg.getbusinessdays();

END;

 

And allrows would be a collection that I can iterate over at my leisure. I have to problem writing future code to just do a for loop over the select, but while migrating existing code I'd rather keep it as intact as possible.





 

 

 

 

 

 

 

1) If you declare a return type setof TABLENAME the resultset will contain rows with field definitions like the table.

 
2) To call the function from another plpgsql function use:
 
declare
    row record
begin
    for row in select * from dates_pkg.getbusinessdays(...) Loop
        ...process...
    end loop
...
end
 

see http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING

 
 
On 06/28/2011 09:34 PM, David Greco wrote:
 

I am porting some Oracle code to PLPGSQL and am having a problem with functions that return SETOF datatype. In Oracle, the functions I'm porting return a TABLE of TYPE datatype, this TABLE being itself a named type. I am not aware of how to do this in PLPGSQL.

 
Consider a function with header:
 

CREATE OR REPLACE FUNCTION dates_pkg.getbusinessdays(pstartdate timestamp with time zone, penddate timestamp with time zone) RETURNS SETOF timestamp with time zone AS

 
I can easily call this function in SQL like so:
 

select * from dates_pkg.getbusinessdays( now(), now() + INTERVAL '7' day ) as business_day;

 

However, I can't figure out how to call this function from another plpgsql function. Any hints?

 
~Dave Greco

 


pgsql-general by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: point types in "DISTINCT" queries
Next
From: Magnus Hagander
Date:
Subject: Re: point types in "DISTINCT" queries