Re: How to pass ad-hoc "complex" data structures?

On Nov 6, 2012, at 9:24 AM, Tim Landscheidt <[email protected]> wrote:

>  SELECT t1.ID AS SurplusID,
> |        t1.Name AS SurplusName,
> |        t2.ID AS MissingID,
> |        t2.Name AS MissingName
> |   FROM tmpData AS t1
> |   FULL OUTER JOIN (VALUES (1, 'Z'), (4, 'E'), (7, 'L'))
> |     AS t2 (ID, Name)
> |   USING (ID, Name)
> |   WHERE t1.ID IS NULL OR t2.ID IS NULL;

You could use VALUES in a table expression, like this:

    SELECT t1.ID AS SurplusID,
           t1.Name AS SurplusName,
           t2.ID AS MissingID,
           t2.Name AS MissingName
      FROM (VALUES
              (1, 'FOO'),
              (2, 'bar')
           ) AS t1(id, name)
       FULL OUTER JOIN (VALUES (1, 'Z'), (4, 'E'), (7, 'L'))  AS t2 (ID, Name)
      USING (ID, Name)
      WHERE t1.ID IS NULL OR t2.ID IS NULL;

Best,

David