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/