Re: proposal: array utility functions phase 1 - Mailing list pgsql-hackers
From | Joe Conway |
---|---|
Subject | Re: proposal: array utility functions phase 1 |
Date | |
Msg-id | 3DF563FD.8040601@joeconway.com Whole thread Raw |
In response to | Re: proposal: array utility functions phase 1 (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-hackers |
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
pgsql-hackers by date: