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?
--
Sam http://samason.me.uk/