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?
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.
