Postgres - returning row id's into stored procedure variable causes error

Question

I have a stored procedure which I'm trying to use to delete several rows of a table based on an array of id's, from the rows that are deleted I want to return those id's and store them in a variable so that it can be used in another delete statement. Here's a reduced segment of my function.

create or replace function "table_a"."deletes"
    (p_ids int[]    
    )
returns int
as $$
declare
    v_directories int[];
begin
    
delete from "table_a"."foo" where "hoo_id" = any(unnest(p_ids)) returning id into v_dirs;
delete from "table_a"."bar" where "foo_id" = any(unnest(v_dirs));

        return 1;
        
        exception
    when others
    then raise exception '% %', sqlstate, sqlerrm;
end;
$$ LANGUAGE plpgsql;

This gives me an error of -

'set-returning functions are not allowed in WHERE'

What am I missing?

Gordon Linoff · Accepted Answer · 2020-08-26 22:30:07Z

1

Use a CTE instead:

with ids as (
      delete from "table_a"."foo"
          where "hoo_id" = any(unnest(p_ids))
          returning id 
     )
delete from "table_a"."bar"
     where "foo_id" in (select id from ids);

answered Aug 26, 2020 at 22:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Chris Over a year ago

Am I able to use the 'ids' for multiple deletes with this methods? I've edited the original post to demonstrate what I mean with your approach. Thanks.

Chris Over a year ago

Nevermind, my formatting was incorrect, got it thanks.

Collectives™ on Stack Overflow

Postgres - returning row id's into stored procedure variable causes error

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related