0

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?

Niayesh Isky
  • 1,100
  • 11
  • 17

1 Answers1

2

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.

Michel Milezzi
  • 10,087
  • 3
  • 21
  • 36