CODEWITHSUNDEEP
sqlpostgresqlsql-drop
user2771738
user2771738

Reputation: 951

pgSQL, dynamic drop tables statement

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").

Upvotes: 0

Views: 1916

Answers (1)

MatheusOl
MatheusOl

Reputation: 11815

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).

Upvotes: 2

Related Questions