[SOLVED] Postgres DROP TABLE using DO DECLARE and EXECUTE

Postgres DROP TABLE using DO DECLARE and EXECUTE

I'm trying to drop tables returned from a query using EXECUTE. Here's an example:

CREATE TABLE test_a (id BIGINT);
CREATE TABLE test_b (id BIGINT);

DO
$f$
DECLARE command TEXT;
BEGIN
SELECT INTO command 'SELECT ARRAY_TO_STRING(ARRAY_AGG($$DROP TABLE $$ || table_name), $$;$$) FROM information_schema.tables WHERE table_name ILIKE $$test%$$';
EXECUTE command;
END;
$f$;

The SELECT statement returns "DROP TABLE test_a; DROP TABLE test_b", which I'm passing into the declared variable and trying to run using EXECUTE, but to no effect. What am I doing wrong?

PostgreSQL 9.5.18, compiled by Visual C++ build 1800, 64-bit

Solution

You are storing the string SELECT ARRAY_TO_STRING ... in that variable, not the result of the SELECT statement.

You can also simplify ARRAY_TO_STRING(ARRAY_AGG(..)) to string_agg() and it's highly recommended to use format() to generate dynamic SQL, to properly deal with identifiers that need quoting.

Use the following:

DO
$f$
DECLARE 
  command TEXT;
BEGIN

 SELECT string_agg(format('drop table %I', table_name), ';')
   INTO command
 FROM information_schema.tables 
 WHERE table_name ILIKE 'test%';

 execute command;
END;
$f$;