Re: PL/pgSQL and SETOF

On Dec 3, 2007 3:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > > no. Your simplest version is historic relict and is available only in
> > > sql language. I am not sure, maybe in C language too.
> >
> > It is extremely useful to be able call functions in this way. I
> > really wish it were possible to do this with pl/sql functions as
> > well...
> >
>
> where is this form better, than standard form? It's similar to CALL
> statement, but I am sorry, it's inconsistent, and it do problems
> mainly for beginners.

as you noted, a principal draw of this syntax isf invoking functions
like procedures. if we had true stored procedures, this argument
would probably go away. However, it also allows expressing some
complex queries in a simpler, faster way. The payoff here is in some
really tricky cases. Here is an example (note the expansion of the
set via the SRF 'explode array'). This isn't the only way to do this,
but is the best in PostgreSQL terms (AFAICT).

select q.item_lookup, is_child, p.* from item p
join
(
select
item_id as item_lookup,
false as is_child,
explode_array(parents) as item_id
from item
union all
select
l.item_id as item_lookup,
true as is_child,
r.item_id
from item l
join item r on r.parents between (l.parents || 0) and (l.parents
|| 2147483647)
) q using (item_id);

for the full context where I used that query, go to
http://merlinmoncure.blogspot.com/2007/09/one-of-my-favorite-problems-in.html

The really odd thing to me is that multiple SRF as field functions
expand to LCM number of rows. OTOH, this gives us a brain busting
(seasonally themed) way to count up the 12 days of Christmas :D

create or replace function f(i int) returns setof int as
$$
select generate_series(1, $1);
$$ language sql;

select sum((v).f) from (select f(f(12))) v;
sum
-----
364
(1 row)

merlin