pgSQL, dynamic drop tables statement

Question

i want to dynamically delete some tables from database. I select them first, and then i am trying to use delete statement but it doesn't works.

tmp TEXT;
FOR tmp IN SELECT names from Garbage
LOOP
    DROP TABLE tmp;
END LOOP;

but unfortuntly i got errors at drop statement. It always trying to delete table "tmp" instead of deleting the value of tmp(which are some strings like "table1" "table2").

Try casting it and prepending `perform`, i.e. `perform drop table tmp::regclass`. Better yet, use temporary tables -- they'll get dropped automatically at the end of your transaction. — Denis de Bernardy, Sep 12 '13 at 08:14
About the temp tables(they should be created at begining as temp?), I dont know how many tables are in garbage, and i can't determine which tables could be sent to delete in future so i would prefer to use drop statement. — user2771738, Sep 12 '13 at 09:02
If it's temporary stuff for a single transaction, create it like instead: `create temporary table ...`. That way, you don't need to be worry about garbage collecting it when you're done. — Denis de Bernardy, Sep 12 '13 at 09:45
but the problem is that i dont know if any of thoose tables are temp. Anyway i used execute String with drop statement :) — user2771738, Sep 12 '13 at 09:53

score 2 · Answer 1 · answered Sep 12 '13 at 11:48

You will need a dynamic query to be executed. To do that, you need to construct your query and call EXECUTE:

DECLARE
    tmp TEXT;
    ...
BEGIN
...
FOR tmp IN SELECT names FROM Garbage
LOOP
    EXECUTE 'DROP TABLE ' || quote_ident(tmp);
END LOOP; 
...

Notice that I used quote_ident to escape the name properly, it is better and safer. But, if you created a table, let's say named MyTable but didn't quoted (double-quotes) it on creation time, PostgreSQL also store its name as mytable, so quote_ident('MyTable') will generate "MyTable" which doesn't exists, you should take care of it (e.g. always use the lowercase names or always quote your IDs).

pgSQL, dynamic drop tables statement

1 Answers1