Re: How to join function with a table?

On 8/5/05, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Certainly not --- per the SQL spec, different elements of a FROM list
> are independent, so the datelist relation can't refer to P.
> (I think SQL 2003 has a construct called LATERAL that would allow
> such things, but we don't implement that yet.)
>
> The only way to do this at the moment in Postgres is to put the
> set-returning function into the SELECT target list:
>
> select id, datelist(date_start, date_end) from payment;
>
> which will work fine if datelist() is implemented as a SQL function,
> and not so fine if it's implemented in plpgsql. You can work around
> this by wrapping the plpgsql function in a SQL function (ick).
> I posted an example in another thread a day or so ago.
>
> regards, tom lane
>
This wraping works! Thanks Tom.
create function datelist_sql(date, date) returns setof date as'

select * from datelist($1,$2)

'language 'sql' strict immutable;
Then simply do this query
"select datelist('8/1/2005', '8/6/2005')"
otherwise with plpgsql function I got
ERROR: set-valued function called in context that cannot accept a set