Re: PLPGSQL SETOF functions

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
>