Re: join from array or cursor

On Fri, Aug 21, 2009 at 1:13 PM, Sam Mason<sam(at)samason(dot)me(dot)uk> wrote:
> On Fri, Aug 21, 2009 at 12:05:51PM -0400, Tom Lane wrote:
>> Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
>> > ... PG should instead arrange that the expression
>> > "t" is run exactly once and reuse the single result for all columns.
>>
>> We might be able to do that based on the row-returning-subselect
>> infrastructure being discussed over here:
>> http://archives.postgresql.org/message-id/4087.1250867036@sss.pgh.pa.us
>
> Huh, fun.  Not sure if I'm interpreting this correctly, but what would
> the difference in semantics between:
>
>  SELECT (SELECT 1,2);
>
> and
>
>  SELECT (SELECT (1,2));

The first form is not allowed because subqueries used that way must
return only one column. The second form works because the extra
parens constructs a row type which gets around that restriction.

> PG seems to make the distinction somewhat blurry at the moment.  For
> example, upthread I did:
>
>  SELECT (id((SELECT (1,2)::foo))).*;
>
> and got back two columns, and yet when I do what I think is equivalent:
>
>  SELECT x.*
>  FROM (SELECT (1,2)::foo) x;

That is _not_ equivalent. You are asking for all the columns from the
table expression 's'...there is one column, a composite type with two
fields. The equivalent would have been:

SELECT (x).foo.* ...

I think may have missed the point of what I was saying earlier. In
any query, when you have an expression of (x).*, if x is a row type of
some kind, the expression is rewritten on the fly as:

(x),a, (x).b, ... for each field in x. That's why (in say, a rule):

select (NEW).* is distinct from (OLD).*
is not future proofed against changes in the row type while:
select NEW is distinct from OLD

is. in the first form the query is expanded on rule creation and will
not pick up extra columns if they are added. think of: .* as a macro
that is rewritten by the query parser.

select ((1,2,nextval('v_seq'))::foo).*; if it was allowed, in current
methods would probably expand into:

select ((1,2,nextval('v_seq'))::foo).a,
((1,2,nextval('v_seq'))::foo).b, ((1,2,nextval('v_seq'))::foo).c;

if foo had fields a,b,c. Hilarity ensues :-).

What I was trying to say before is that maybe .* should not expand the
query that way, but should just give you the fields of that type and,
(very) controversially, do that in view and rule plans as well.

merlin