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?

Craig Ringer · Answer 1 · 2018-11-21T03:10:37.387

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)

Where does `f.*` come from? I didn't know a lateral join could be implicit. I think that is not good and should not be even mentioned :) Just to be sure: 9.3 does not do multiple evaluation of `select (f()).*`? — Clodoaldo Neto, Jun 27 '14 at 12:07
@ClodoaldoNeto Fixed missing alias. As for multiple eval ... 9.3 *does* multiply evaluate f for each col returned by row-returning `f` in `(f()).*`. Lateral queries don't. — Craig Ringer, Jun 27 '14 at 14:10
As for `LATERAL` being implicit - it is per spec. The `LATERAL` keyword is just noise there - which seems reasonable as there is nowhere else parameters for a function could come from. I guess it's worth showing as inline docs but then people get confused when they see a similar query without it. — Craig Ringer, Jun 27 '14 at 14:25
Worth emphasizing because i've been bitten by it. WARNING: calling `select (myfunc('param')).*` will execute the function multiple times (for each column returned as mentioned above by @CraigRinger) — sm_, Nov 21 '18 at 02:18
@SirajMansour You can actually work around that with another layer of subqueries: you have the first layer evaluate the function and return a row-valued result, then the second layer expand it. The cause is that `*` does very simple macro-like expansion, where `(expr).*` expands to `(expr).col1, (expr).col2, ...`. But since lateral queries don't suffer from this issue, there's not much point worrying about it now, just avoid `(expr).*` entirely. — Craig Ringer, Nov 21 '18 at 02:59
@SirajMansour ... but I'm a sucker for testing things so I've expanded the answer to clearly illustrate the issue and why you should prefer lateral, or if you can't, use the subquery nesting workaround. — Craig Ringer, Nov 21 '18 at 03:12

Dwayne Towell · Answer 2 · 2014-06-27T17:15:25.383

-1

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

The subselect SELECT some_function(thing_id) FROM things returns a row for each record found. The outer select "uncompresses" the row into separate columns.

edited Jun 27 '14 at 17:15

answered Jun 27 '14 at 05:09

Dwayne Towell

8,154
4
36
49

please add some explation to your answer so that OP and Other user may understand better – sundar nataraj Jun 27 '14 at 06:34

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

2 Answers2

9.3 and above: lateral query

Pre-9.3: wildcard expansion (with care)

Demo:

Linked