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 ?

Praveen · Accepted Answer · 2016-02-13 19:24:31Z

2

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

edited Feb 13, 2016 at 19:24

answered Feb 13, 2016 at 19:06

Praveen

9,4534 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mohamed Taboubi Over a year ago

when I execute this command it says : ERROR: loop variable of loop over rows must be a record or row variable or list of scalar variables LINE 8: FOR i IN ^ ********** Erreur **********

Mohamed Taboubi Over a year ago

yes same error... in fact it indicates error on "i" (in the line FOR i IN), :(

Mohamed Taboubi Over a year ago

Should i be declared somewhere ? because it is like it should be declared !

Mohamed Taboubi Over a year ago

Oh yes, I added : DECLARE i text; and it passes .. however it do not execute i.stmt ... it says : ERROR: missing FROM-clause entry for table "i" LINE 1: SELECT i.stmt

Mohamed Taboubi Over a year ago

So gooood , thank you :) so the issue in the first script was "i.stmt" should be "i" instead :)

Collectives™ on Stack Overflow

Create script to drop several tables (PostgreSQL)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related