On Fri, Aug 21, 2009 at 10:17 AM, Sam Mason<sam@xxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
