Reduce duplication in postgres plpgsql function with CTE

Question

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?

I"m just baffled. another_function in one case takes one argument and in another two, where the second argument is . . . .what? A CTE? A tuple? I think you should ask a new question with more specifics about what you want to accomplish. — Gordon Linoff
– Gordon Linoff, Commented May 1, 2020 at 19:18

Gordon Linoff · Accepted Answer · 2020-05-01 19:20:52Z

1

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;

answered May 1, 2020 at 19:20

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user779159 Over a year ago

Thanks and do you know how I can get the first query in my question to not throw query has no destination for result data error when it's defined as returns boolean? It works when using sql function but not plpgsql.

Gordon Linoff Over a year ago

@user779159 . . . If this is inside a function, you need to store the value somewhere.

user779159 Over a year ago

Regarding the query has no destination for result data error, if it was a regular sql function with returns boolean my first query would work. But if I change it to a plpgsql function then I get the error. Normally I can return a boolean from a plpgsql function by doing something like return (select exists(.......)). Why isn't that possible here? If I wrap the whole with in a return I get WITH clause containing a data-modifying statement must be at the top level.

Collectives™ on Stack Overflow

Reduce duplication in postgres plpgsql function with CTE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related