CODEWITHSUNDEEP
sqldatabasepostgresqlset-returning-functionssql-returning
Daniel Shin
Daniel Shin

Reputation: 5206

RETURNING rows using unnest()?

I'm trying to return a set of rows after doing UPDATE.

Something like this.

UPDATE Notis new_noti SET notis = '{}'::noti_record_type[] 
FROM (SELECT * FROM Notis WHERE user_id = 2 FOR UPDATE) old_noti 
WHERE old_noti.user_id = new_noti.user_id RETURNING unnest(old_noti.notis);

but postgres complains, rightly so:

set-valued function called in context that cannot accept a set

How am I supposed to go about implementing this?

That is, RETURNING a set of rows from SELECTed array after UPDATE?

I'm aware that a function can achieve this using RETURNS SETOF but rather prefer not to if possible.

Upvotes: 4

Views: 5122

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

Use a data-modifying CTE.
You can use a set-returning function in the SELECT list, but it is cleaner to move it to the FROM list with a LATERAL subquery since Postgres 9.3. Especially if you need to extract multiple columns (from a row type like you commented). It would also be inefficient to call unnest() multiple times.

WITH upd AS (
   UPDATE notis n
   SET    notis = '{}'::noti_record_type[]  -- explicit cast optional
   FROM  (
      SELECT user_id, notis
      FROM   notis
      WHERE  user_id = 2
      FOR    UPDATE
      ) old_n
    WHERE old_n.user_id = n.user_id
    RETURNING old_n.notis
    )
SELECT n.*
FROM   upd u, unnest(u.notis) n;  -- implicit CROSS JOIN LATERAL

If the array can be empty and you want to preserve empty / NULL results use LEFT JOIN LATERAL ... ON true. See:

Also, multiple set-returning functions in the same SELECT can exhibit surprising behavior. Avoid that. This has been sanitized with Postgres 10. See:

Alternative to unnest multiple arrays in parallel before and after Postgres 10:

Related:

Behavior of composite / row values

Postgres has an oddity when assigning a row type (or composite or record type) from a set-returning function to a column list. One might expect that the row-type field is treated as one column and assigned to the respective column, but that is not so. It is decomposed automatically (one row-layer only!) and assigned element-by-element.

So this does not work as expected:

SELECT (my_row).*
FROM   upd u, unnest(u.notis) n(my_row);

But this does (like @klin commented):

SELECT (my_row).*
FROM   upd u, unnest(u.notis) my_row;

Or the simpler version I ended up using:

SELECT n.*
FROM   upd u, unnest(u.notis) n;

Another oddity: A composite (or row) type with a single field is decomposed automatically. Thus, table alias and column alias end up doing the same in the outer SELECT list:

SELECT n FROM unnest(ARRAY[1,2,3]) n;
SELECT n FROM unnest(ARRAY[1,2,3]) n(n);
SELECT n FROM unnest(ARRAY[1,2,3]) t(n);
SELECT t FROM unnest(ARRAY[1,2,3]) t(n);  -- except output column name is "t"

For more than one field, the row-wrapper is preserved:

SELECT t FROM unnest(ARRAY[1,2,3]) WITH ORDINALITY t(n);  -- requires 9.4+

Confused? There is more. For composite types (the case at hand) like:

CREATE TYPE my_type AS (id int, txt text);

While this works as expected:

SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) n;

You are in for a surprise here:

SELECT n FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) n(n);

And that's the error I had: When providing a column list, Postgres decomposes the row and assigns provided names one-by-one. Referring to n in the SELECT list does not return the composite type, but only the (renamed) first element. I had mistakenly expected the row type and tried to decompose with (my_row).* - which only returns the first element nonetheless.

Then again:

SELECT t FROM unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]) t(n);

(Be aware that the first element has been renamed to "n"!)

With the new form of unnest() taking multiple array arguments (Postgres 9.4+):

SELECT *
FROM   unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
            , ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n;

Column aliases only for the first two output columns:

SELECT *
FROM   unnest(ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
            , ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n(a, b);

Column aliases for all output columns:

SELECT *
FROM   unnest(ARRAY[(1,'foo')::my_type, (2,'bar')::my_type]
            , ARRAY[(3,'baz')::my_type, (4,'bak')::my_type]) n(a,b,c,d);

db<>fiddle here
Old sqlfiddle

Upvotes: 3

Javier Loza
Javier Loza

Reputation: 1

Probably For:

SELECT *
FROM   unnest (ARRAY[(1, 'foo')::my_type, (2, 'bar')::my_type]
             , ARRAY[(3, 'baz')::my_type, (4, 'bak')::my_type]) n(a, b);

Use:

SELECT *
FROM   unnest (ARRAY[(1, 'foo')::text, (2, 'bar')::text]
             , ARRAY[(3, 'baz')::text, (4, 'bak')::text]) WITH ORDINALITY AS t(first_col, second_col);

Upvotes: 0

klin
klin

Reputation: 121494

Use WITH statement:

WITH upd AS (
    UPDATE Notis new_noti SET notis = '{}'::noti_record_type[] 
    FROM (SELECT * FROM Notis WHERE user_id = 2 FOR UPDATE) old_noti 
    WHERE old_noti.user_id = new_noti.user_id RETURNING old_noti.notis
    )
SELECT unnest(notis) FROM upd;

Upvotes: 4

Related Questions