Delete Rows in several tables dynamically in Postgres

Asked

Viewed 453 times

2

I need to delete to clean the database automatically in tables that are within different schemas, "public" and "cine";

I have the following query that returns me 

SELECT concat('"',table_schema,'"', '.', '"',table_name, '"') as 
      table_name
FROM information_schema.columns
WHERE table_schema = 'public' OR table_schema = 'cine' AND 
column_name = 'cine_uuid'
GROUP BY table_name, table_schema;

This, returns me all schemas and tables that have as column "cine_uuid". It is possible to delete as follows:

DELETE FROM [TABELAS] WHERE cine_uuid = '00000000-0000-0000-0000-000000000000'

What I intended was that from a query, to delete the various tables in the various schemas.

It is possible?

database postgresql delete

  • Claudio, there’s no way in one QUERY you delete the record from two Tables, you would need to delete the record one by one. The most you can do is if in one of the tables you could make one DELETE CASCADE, but then a table should have a relationship (FK) with the other.

  • I believe that you have how to do with plsql, I will try to do. When I needed it I did it in C#, easier for me, and it was using a truncate, so it doesn’t apply

1 answer

1


I did the following function:

Selects all tables, from the informed schemas, which contains the informed column, and traverses by running delete:

CREATE OR REPLACE FUNCTION public.delete_from_tables (id varchar 
)
RETURNS pg_catalog.void AS
$body$
    declare
       temprow record;
       begin

       FOR temprow IN
        (select 
            t.table_name
            from information_schema.tables t
            inner join information_schema.columns c on c.table_name = t.table_name and c.column_name = 'cine_uuid '
            where t.table_schema in ('public','cine'))
    LOOP
        EXECUTE 'DELETE FROM '|| temprow.table_name || ' where cine_uuid = ''' || $1 || ''''; 
    END LOOP;

       end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Utilizing:

select delete_from_tables('00000000-0000-0000-0000-000000000000'::varchar);

Browser other questions tagged database postgresql delete

You are not signed in. Login or sign up in order to post.