0

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

douglas_forsell
  • 111
  • 1
  • 10
  • FYI to assign that text to a variable you can just do `command := 'your text'`, no need for select. – 404 Oct 29 '19 at 12:48
  • That command statement is *not* what you expect. Please print it out with a `RAISE NOTICE` to see what it's actually doing. – 404 Oct 29 '19 at 12:51

1 Answers1

3

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