Create script to drop several tables (PostgreSQL)

Question

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

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 ?

Answer 1

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