sqldatabasepostgresqlset-returning-functionssql-returning

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.

Solution

  • 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;