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 SELECT
ed array after UPDATE
?
I’m aware that a function can achieve this using RETURNS SETOF
but rather prefer not to if possible.
Advertisement
Answer
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;