How can I make a postgres SQL function that uses the value from a deleted row in the next query? It's straightforward to do in PLPGSQL by using a variable, but since there's no variables in SQL functions, how can I do it?
delete from mytable where key1 = '1' and key2 = '2' returning otherkey;
update mytable set otherkey = OTHERKEY where key1 = '1' and otherkey = '2';
-- "OTHERKEY" is the value returned by the first query
Is there a way to do it using a subquery?
Postgres supports data modification statements in CTEs -- with the returning clause.
So, you can do pretty much exactly what you want:
with d as (
delete from mytable
where key1 = '1' and key2 = '2'
returning otherkey
)
update mytable
set otherkey = d.OTHERKEY
from d
where key1 = '1' and otherkey = '2';
update statement know that d.OTHERKEY is just a single value rather than multiple values? Couldn't it be multiple values? If I were using it in a WHERE wouldn't I have to say where column in (d.OTHERKEY) rather than where column = d.OTHERKEY?
delete and update in Postgres. Your question is specifically in the singular "a deleted row". Your question does not address the connection between the tables. However, if there is a primary key, then you would include that in the where condition.returning returns one row then postgres lets you use it after an equals sign to check for equality. But if it returns more than one row then the query will give an error at runtime?OTHERKEY from one of the rows in d.