PLPGSQL SETOF functions

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

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
willcontain 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

seehttp://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
withfunctions that return SETOF datatype. In Oracle, the functions
I'mporting return a TABLE of TYPE datatype, this TABLE being itself
anamed type. I am not aware of how to do this in PLPGSQL.

Consider a function with header:

CREATE OR REPLACE FUNCTIONdates_pkg.getbusinessdays(pstartdate
timestamp with time zone,penddate timestamp with time zone) RETURNS
SETOF timestamp with timezone 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
anotherplpgsql function. Any hints?

~Dave Greco