plpgsql text[] varchar[] array not working

Question

My purpose is assigning the name of the tables into an array and drop them in the foreach loop via this array.

I am actually trying to do something more complicated but before I try to get the following code working:

CREATE OR REPLACE FUNCTION delete_auto()
  RETURNS void AS
$BODY$DECLARE
t text;
tbl_array text[] = array["ID: 889197824 CH: 0006 Messdaten2","ID: 889197824 CH: 0006 Messdaten3","ID: 889197824 CH: 0006 Messdaten4"];
BEGIN 

FOREACH t IN ARRAY tbl_array LOOP
DROP TABLE t;
END LOOP;
END; $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION delete_auto()
  OWNER TO postgres;

Function seems to me pretty correct but it does not drop the tables, does nothing actually after I execute it. I just get such an error on the log:

Table Â»tÂ« does not exist.

I have also tried the same code with varchar[] instead of text[] but not working either. Any help would be appreciated

Pavel Stehule Pavel Stehule · Accepted Answer · 2013-05-23T10:17:15

There are a few errors:

you cannot use double quotes for string literals (it is used for case sensitive SQL identifiers)
you should use dynamic SQL for parametrized DDL statements (statement EXECUTE). DDL statements does not have execution plan and these statements doesn't support parametrization (you should not use variables there)
```
DO $$
DECLARE
  tables varchar[] := ARRAY['t1','t2'];
  t varchar;
BEGIN
  FOREACH t IN ARRAY tables
  LOOP
    EXECUTE format('DROP TABLE %I', t);
  END LOOP;
END;
$$;
```

plpgsql text[] varchar[] array not working

2 Answers