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
– Denis de Bernardy, Commented Sep 12, 2013 at 8: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
– user2771738, Commented Sep 12, 2013 at 9: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
– Denis de Bernardy, Commented Sep 12, 2013 at 9: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
– user2771738, Commented Sep 12, 2013 at 9:53

MatheusOl · Accepted Answer · 2013-09-12 11:48:05Z

2

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

answered Sep 12, 2013 at 11:48

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pgSQL, dynamic drop tables statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related