CODEWITHSUNDEEP
sqlpostgresql
Mohamed Taboubi
Mohamed Taboubi

Reputation: 7021

Create script to drop several tables (PostgreSQL)

I want to create a script that detect and drop the public tables in my posgresql database...

The request I build is the following :

SELECT CONCAT('DROP TABLE ', table_schema,'.',table_name,';') AS stmt FROM information_schema.TABLES
WHERE table_schema='public' AND table_catalog='capsana'

Here is a screenshot of the output I got

enter image description here

I want now to execute the commands (in the stmt column) in an automatic way .. without doing copy paste !

Is there any way to do that ?

Upvotes: 2

Views: 669

Answers (1)

Praveen
Praveen

Reputation: 9345

You can use dynamic sql to do this;

DO $$
DECLARE
    drop_stmt text;
BEGIN
    FOR drop_stmt IN 
    SELECT CONCAT('DROP TABLE ', table_schema,'.',table_name) AS stmt 
    FROM information_schema.TABLES
    WHERE table_schema='public' AND table_catalog='capsana' LOOP
        EXECUTE drop_stmt;
    END LOOP;
END$$;

Upvotes: 2

Related Questions