CODEWITHSUNDEEP
sqlpostgresqlsql-function
user779159
user779159

Reputation: 9602

How to make postgres SQL function that uses value from deleted row?

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?

Upvotes: 0

Views: 156

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

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

Upvotes: 2

Related Questions