On Fri, Aug 21, 2009 at 10:17 AM, Sam Mason<sam@samason.me.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
