CODEWITHSUNDEEP
sqlpostgresqlplpgsql
wasilikoslow
wasilikoslow

Reputation: 1973

Delete from multiple tables using PL/PGSQL

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:

roles

film_person:

film_person

top_level_films:

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

Upvotes: 0

Views: 256

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

wasilikoslow
wasilikoslow

Reputation: 1973

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

Upvotes: 0

Related Questions