CODEWITHSUNDEEP
sqlpostgresql
Niayesh Isky
Niayesh Isky

Reputation: 1230

Executing a script generated by an SQL Query

I'm fairly new to Postgres, and I'm wondering if there is a way to run strings as queries (not in PL/pgSQL, I get that that's not something you can do in the psql prompt). Specifically, in questions such as this one, you generate a batch script using a query, like this:

select 'drop table if exists "' || tablename || '" cascade;' from pg_tables;

But how do you run it?

Upvotes: 0

Views: 203

Answers (1)

Michel Milezzi
Michel Milezzi

Reputation: 11175

Why not using plpgsql? You can do it using anonymous blocks:

DO $$
DECLARE 
    r record;
BEGIN
    FOR r IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'my_schema'
    LOOP
        RAISE INFO 'Dropping table: %.%', r.schemaname, r.tablename;
        EXECUTE format('DROP TABLE %I.%I', r.schemaname, r.tablename);
    END LOOP;
END$$;

And yes, it works on psql.

Upvotes: 2

Related Questions