Search Postgresql Archives

Re: PLPGSQL SETOF functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

 



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux