Do rows delete in several tables dynamically, in Postgres

Question

Do rows delete in several tables dynamically, in Postgres

Navigation

#1 by (1 votes)

2

I need to do a delete to clean the database automatically on tables that are within different schemas, the "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 the schemas and tables that have as "cine_uuid" column. It is possible to delete as follows:

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

What I wanted was for a query to do a delete on several tables in the various schemas.

Is it possible?

delete postgresql

asked by anonymous 23.03.2018 / 17:57

1 answer

Link Anchors, how to let it open in a certain position of the page How to Calculate CRC Modbus RTU

score 1 · Accepted Answer

I've done the following function:

Selects all tables, from the informed schemas, which contains the entered column, and runs through the 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;

Usage:

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