CODEWITHSUNDEEP
sqlpostgresqlplpgsqlcommon-table-expression
user779159
user779159

Reputation: 9622

Reduce duplication in postgres plpgsql function with CTE

In a SQL function I can return a boolean if I do

with myquery as (delete from mytable where id = 'value1' returning 1)
select exists (select * from another_function('value2') where (select count(*) from myquery) > 0);

But in a plpgsql function it doesn't work and gives error query has no destination for result data.

In this function I want to execute another_function if any rows were actually deleted from mytable. The problem is I'm repeating the entire select exists part, because I'm using that in multiple functions.

Is it possible to move more of that logic into another_function? So that I can do something like this?

with myquery as (delete from mytable where id = 'value1' returning 1)
select * from another_function('value2', myquery)

How can I pass the CTE into a function so I don't need to repeat the select exists and where (select count(*) from myquery) > 0) every time I want to call another_function?

Upvotes: 0

Views: 85

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

I would expect an auxiliary function to take an argument, such as the id being returned from the delete. It would then look like:

with d as (
      delete from mytable
      where id = 'value1'
      returning id
     )
select another_function('value2', d.id)
from d;

This operates one value at a time, but that is typically what one would want. If you wanted all ids passed in at once, you could use an array:

with d as (
      delete from mytable
      where id = 'value1'
      returning id
     )
select another_function('value2', array_agg(d.id))
from d;

Upvotes: 1

Related Questions