Re: proposal: array utility functions phase 1

Tom Lane wrote:
> This seems fairly unworkable to me as-is.  By definition, WHERE selects
> from a cross-product of the FROM tables; to make the above do what you
> want, you'd have to break that fundamental semantics.  The semantics of
> explicit JOIN cases would be broken too.
> 
> What we need is some kind of explicit multi-level SELECT operation.
> Perhaps it would help to think about the analogy of aggregates of
> aggregate functions, which are impossible to express properly in a
> single SELECT but work nicely given subselect-in-FROM.
> Subselect-in-FROM doesn't seem to get this job done though.
> 
> Right offhand I don't see any reasonable syntax other than
> function-in-the-SELECT-list, which shoots us right back into the
> messinesses of the Berkeley implementation.  However, we do now have the
> precedent of the table-function AS clause.  Does it help any to do
> something like
> 
>     SELECT grosysid, array_values(grolist) AS (array_index,member_id)
>     FROM pg_group where groname = 'g2';

After further thought, and ignoring the difficulty of implementation, what 
seems ideal is to be able to specify 'setof <datatype>' or 'setof 
<composite-type>' as an input to the function, and fire the function once for 
each row of the input. Basically, allow anything that now qualifies as a FROM 
item -- a table reference, a subselect with AS clause, another table function, 
or maybe even a join clause. Some (totally contrived) examples of how it would 
look:

create table foo1(f1 int, f2 text);
insert into foo1 values(1,'a');
insert into foo1 values(2,'b');
insert into foo1 values(3,'c');

create table foo2(f1 int, f2 text);
insert into foo2 values(1,'w');
insert into foo2 values(1,'x');
insert into foo2 values(2,'y');
insert into foo2 values(2,'z');

create function funcfoo1(setof foo1) returns setof foo2 as 'select * from foo2 
where foo2.f1 = $1.f1' language 'sql';
select * from funcfoo1(foo1); f1   f2
----+----- 1  | w 1  | x 2  | y 2  | z

select * from funcfoo1((select * from foo1 where f1=1) as t); f1   f2
----+----- 1  | w 1  | x


What do you think?


> (Again you could wrap this in an outer SELECT to transform the
> member_ids to member_names.)
> 
> The real problem with the Berkeley approach shows up when you consider
> what happens with multiple table functions called in a single SELECT.
> The code we currently have produces the cross-product of the implied
> rows (or at least it tries to, I seem to recall that it doesn't
> necessarily get it right).  That's pretty unpleasant, and though you can
> filter the rows in an outer SELECT, there's no way to optimize the
> implementation into a smarter-than-nested-loop join.

What if there was a way to declare that a table function returns sorted 
results, and on which column(s)?


> It seems like somehow we need a level of FROM/WHERE producing some base
> rows, and then a set of table function calls to apply to each of the
> base rows, and then another level of WHERE to filter the results of the
> function calls (in particular to provide join conditions to identify
> which rows to match up in the function outputs).  I don't see any way to
> do this without inventing new SELECT clauses out of whole cloth
> ... unless SQL99's WITH clause helps, but I don't think it does ...

Is this still needed given my approach above?


>>How ugly/difficult would it be to allow the planner to interrogate the 
>>function and let the function report back a tupledesc based on the actual 
>>runtime input parameters?
> 
> 
> Parse-time, not run-time.  It could be done --- IIRC, the auxiliary
> "function info" call we introduced in the V1 fmgr protocol was
> deliberately designed to allow expansion in this sort of direction.
> But it would have to take a tupledesc (or some similar static
> description) and return another one.

Nice! I'll dig in to that a bit.

Thanks,

Joe