Re: PLPGSQL SETOF functions

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