Re: [Fwd: SETOF input parameters (was Re: [HACKERS] proposal:

Tom Lane wrote:
> A more serious objection is that this doesn't really address the
> fundamental issue, namely that you can't drive a SRF from the results of
> a query, except indirectly via single-purpose function definitions (like
> test2() in your example).

True enough. I've struggled trying to come up with a better way.

> I'm leaning more and more to the thought that we should reconsider the
> Berkeley approach.

The problem with the Berkley approach is what to do if there are two SRFs in
the target list.

Suppose

f(t1.x) returns:
1 a z
2 b y

and g(t2.y) returns:
3 q
5 w
7 e

and *without* the SRFs the query
select * from t1 join t2 on t1.id = t2.id;
would return:
id | x | id | y
------+------+------+------
4 | k | 4 | d
6 | v | 6 | u

What do we do for
select f(t1.x), g(t2.y), * from t1 join t2 on t1.id = t2.id;
?

Should we return 2 x 2 x 3 rows? Or do we impose a limit of 1 SRF in the
target list?

> Another line of thought is to consider the possibilities of subselects
> in the target list. For example,
>
> SELECT ..., (SELECT ... FROM mysrf(a, b)) FROM foo WHERE ...;
> I believe it's already the case that foo.a and foo.b can be transmitted
> as arguments to mysrf() with this notation. The restriction is that the
> sub-select can only return a single value (one row, one column) to the
> outer query. It doesn't seem too outlandish to allow multiple columns
> to be pulled up into the outer SELECT's result list given the above
> syntax. I'm less sure about allowing multiple rows though.

This suffers from the same problem if there can be more than one subselect in
the target list (if multiple rows is allowed).

> Any thoughts?

Is it too ugly to allow:
select ... from (select mysrf(foo.a, foo.b) from foo) as t;

where the Berkley syntax is restricted to where both are true:
1. a single target -- the srf
2. in a FROM clause subselect

In this case we could still use the column reference syntax too:
select ... from (select mysrf(foo.a, foo.b) from foo) as t(f1 int, f2 text);

But not allow the Berkley syntax for multi-row, multi-column SRFs otherwise.

What do you think?

Joe