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").
You will need a dynamic query to be executed. To do that, you need to construct your query and call
EXECUTE
: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 namedMyTable
but didn't quoted (double-quotes) it on creation time, PostgreSQL also store its name asmytable
, soquote_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).