Re: join from array or cursor

On Fri, Aug 21, 2009 at 10:17 AM, Sam Mason<sam(at)samason(dot)me(dot)uk> wrote:
> On Fri, Aug 21, 2009 at 02:22:54PM +0100, Greg Stark wrote:
>>  SELECT (r).*
>>   FROM (SELECT (SELECT x FROM x WHERE a=id) AS r
>>           FROM unnest(array[1,2]) AS arr(id)
>>        ) AS subq;
>
> Shouldn't that second inner SELECT unnecessary?  I'd be tempted to
> write:
>
>  SELECT ((SELECT x FROM x WHERE x.a = arr.id)).*
>  FROM unnest(array[1,2]) AS arr(id)
>
> but PG throws this out for some reason.  Adding more brackets doesn't
> seem to help, the following seems related:
>
>  SELECT ((SELECT (1,2))).*;
>
> The current grammar seems to require two sets of brackets, one for the
> sub-select and another for pulling the value out of the record.  Not
> quite sure why PG calls it indirection, but I guess that's how it's
> implemented.  I can seem to work around it by doing:
>
>  CREATE FUNCTION id(anyelement)
>      RETURNS anyelement
>      LANGUAGE sql AS $$
>    SELECT $1; $$;
>
>  SELECT (id((1,2))).*;
>
> But this seems nasty and bumps up against the annoying "record type has
> not been registered" that I hit all to often.  More fiddling gets to:
>
>  CREATE TYPE foo AS ( i int, j int );
>
>  SELECT (id((SELECT (1,2)::foo))).*;
>
> or am I missing something obvious?

I think that what you are bumping in to is that there is no real
definition of '*' in the query. 've griped about this a few times. If
type 't' has fields a,b,

select (t).* is expanded to select (t).a, (t).b.

This can lead to some weird situations. If you have an aggregate
function that returns t, for example:

select (agg()).*; will run the aggregate function twice (this is a
_huge_ gotcha!). I think that '*' needs to be promoted somehow so
that it isn't expanded during parsing but has special meaning.

merlin