sql - How can you expand a "condensed" PostgreSQL row into separate columns?

Question

I have a function which returns a table.

If you run SELECT * FROM some_function(12345) the result is:

object_id | name
----------------
    12345 | "B"

If you run SELECT some_function(12345) the result is:

some_function
-------------
(12345,"B")

The problem is that I want the original form (so that I can access individual column values), but have the argument to some_function() come from a column in a table. I can execute SELECT some_function(thing_id) FROM things but this returns:

some_function
-------------
(12345,"B")
(12346,"C")
(12347,"D")

Whereas what I want returned is:

object_id | name
----------------
    12345 | "B"
    12346 | "C"
    12347 | "D"

So how can one "unnest" or "expand" such a condensed row?

Answer 1

9.3 and above: lateral query

In PostgreSQL 9.3 or newer use an implicit lateral query:

SELECT f.* FROM things t, some_function(t.thing_id) f;

Prefer this formulation for all new queries. The above is the standard formulation.

It also works properly with functions that RETURNS TABLE or RETURNS SETOF RECORD as well as funcs with out-params that RETURNS RECORD.

It's shorthand for:

SELECT f.*
FROM things t
CROSS JOIN LATERAL some_function(t.thing_id) f;

Pre-9.3: wildcard expansion (with care)

Prior versions, causes multiple-evaluation of some_function, does not work if some_function returns a set, do not use this:

SELECT (some_function(thing_id)).* FROM things;

Prior versions, avoids multiple-evaluation of some_function using a second layer of indirection. Only use this if you must support quite old PostgreSQL versions.

SELECT (f).*
FROM (
  SELECT some_function(thing_id) f
  FROM things
) sub(f);

Demo:

Setup:

CREATE FUNCTION some_function(i IN integer, x OUT integer, y OUT text, z OUT text) RETURNS record LANGUAGE plpgsql AS $$
BEGIN
  RAISE NOTICE 'evaluated with %',i;
  x := i;
  y := i::text;
  z := 'dummy';
  RETURN;
END;
$$;

create table things(thing_id integer);
insert into things(thing_id) values (1),(2),(3);

test run:

demo=>     SELECT f.* FROM things t, some_function(t.thing_id) f;
NOTICE:  evaluated with 1
NOTICE:  evaluated with 2
NOTICE:  evaluated with 3
 x | y |   z   
---+---+-------
 1 | 1 | dummy
 2 | 2 | dummy
 3 | 3 | dummy
(3 rows)

demo=>     SELECT (some_function(thing_id)).* FROM things;
NOTICE:  evaluated with 1
NOTICE:  evaluated with 1

NOTICE:  evaluated with 1
NOTICE:  evaluated with 2
NOTICE:  evaluated with 2
NOTICE:  evaluated with 2
NOTICE:  evaluated with 3
NOTICE:  evaluated with 3
NOTICE:  evaluated with 3
 x | y |   z   
---+---+-------
 1 | 1 | dummy
 2 | 2 | dummy
 3 | 3 | dummy
(3 rows)

demo=>  SELECT (f).*
    FROM (
      SELECT some_function(thing_id) f
      FROM things
    ) sub(f);
NOTICE:  evaluated with 1
NOTICE:  evaluated with 2
NOTICE:  evaluated with 3
 x | y |   z   
---+---+-------
 1 | 1 | dummy
 2 | 2 | dummy
 3 | 3 | dummy
(3 rows)