Re: join from array or cursor

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/