Delete from multiple tables using PL/PGSQL

Question

I have 3 tables. Which I want to use a postgresql function via giving only film_person_id to delete related roles, films and all other remaining film people from film_person table.

The thing I want to do is, when I call delete_film_person2(15); I want to delete related roles which is role_id : 23 and role_id : 24 also film_person_id : 15 and film_person_id : 16 from film_person table.

here are the tables;

roles:

film_person:

top_level_films:

here is my function

create or replace function delete_film_person2(int)
returns varchar
language plpgsql
as
$$
declare
result varchar;
f int; 
basket_film_id int;
begin
    select top_level_film_id into basket_film_id from roles where film_person_id = $1; 
 
    for f in select film_person_id from roles where top_level_film_id = basket_film_id  
    loop 
    delete from film_person where film_person_id = f.film_person_id;
    end loop;
    result = 'success';
    return result;
end;
$$

but it gives error of;

ERROR:  missing FROM-clause entry for table "f"
LINE 1: f.film_person_id
        ^
QUERY:  f.film_person_id
CONTEXT:  delete_film_person2(integer) PL/pgSQL fonksiyonu, 12. satır, RAISE içinde
SQL state: 42P01

Sample data is better presented as formatted text not as screen shots. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Feb 3, 2022 at 21:56

user330315 · Accepted Answer · 2022-02-03 22:02:22Z

1

You can do this with a single data modifying CTE

create or replace function delete_film_person2(p_film_person_id int)
  returns varchar
as
$$
begin
  with deleted_roles as (
    delete from roles
    where film_person_id = p_film_person_id
    returning top_level_film_id
  ), deleted_films as (
    delete from top_level_films
    where film_id in (select top_level_film_id 
                      from delete_roles)
  )
  delete from film_person
  where film_person_id = p_film_person_id;
  return 'success';
end;
$$ 
language plpgsql;

You wouldn't even need PL/pgSQL for this.

answered Feb 3, 2022 at 22:02

user330315

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

Comments

wasilikoslow · Accepted Answer · 2022-02-03 21:21:26Z

0

changed f variable data type and added 2 more delete queries

returns varchar
language plpgsql
as
$$
declare
result varchar;
f record;
fp_people record;
basket_film_id int;
begin
    select top_level_film_id into basket_film_id from roles where film_person_id = $1; 
     
    for f in select roles.film_person_id from roles where roles.top_level_film_id = basket_film_id  
    loop 
    delete from film_person where film_person.film_person_id = f.film_person_id;
    end loop;
    delete from roles where top_level_film_id = basket_film_id;
    delete from top_level_films where film_id = basket_film_id;
    result = 'success';
    return result;
end;
$$

answered Feb 3, 2022 at 21:21

wasilikoslow

2,0332 gold badges12 silver badges16 bronze badges

Collectives™ on Stack Overflow

Delete from multiple tables using PL/PGSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related