3

I have such query to list tables in current database:

SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)

I am trying to drop all those tables using upper SELECT like subquery:

DROP TABLE IF EXISTS (SELECT c.relname 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE c.relkind 
IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
pg_catalog.pg_table_is_visible(c.oid)) as tname

But that don't work.
How to propery write a query which will drop all tables listed by showed SELECT query?
For now I use DataReader for loop through query result and drop tables by one.
But I think that may go 'at once'.

Wine Too
  • 4,515
  • 22
  • 83
  • 137
  • 1
    If your intention is to drop all objects of the user, this can be done much easier using `drop owned by your_pg_username` –  Jan 26 '14 at 08:27
  • Hm, for those tables owner is 'postgres'. Can that finish good? I would also like to delete my user functions so getting it with subquery will be more useful to my purposes. – Wine Too Jan 26 '14 at 08:34
  • 1
    It's usually not a good idea to use the superuser (postgres) to create your own stuff - and this is one of the reasons why. Another (more important) reason is, that it means that your application connects to the database with superuser privileges which is a security hole wide open. –  Jan 26 '14 at 08:37
  • Yes, I have to change that and I will. – Wine Too Jan 26 '14 at 09:37

2 Answers2

9

You need to use dynamic SQL for this, which in turn can only be used in a procedural language like PL/pgSQL, something like this:

do
$$
declare
   stmt text;
   table_rec record;
begin
   for table_rec in (SELECT c.relname as tname
                     FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                     WHERE c.relkind IN ('r','') 
                       AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
                       AND pg_catalog.pg_table_is_visible(c.oid))
   loop
     execute 'drop table '||table_rec.tname||' cascade';
   end loop;
end;
$$
  • Never before see that so I have to try if it work from .NET and it works. That deletes all wanted tables. So, same way I try to delete my user functions " execute 'drop function '||func_rec.fname||' cascade';" but get error: ERROR: syntax error at or near "cascade" LINE 1: drop function first_agg cascade ^ QUERY: drop function first_agg cascade CONTEXT: PL/pgSQL function inline_code_block line 14 at EXECUTE statement. Can be visible from here why that happens? – Wine Too Jan 26 '14 at 09:25
  • 1
    @user973238: `drop function` doesn't have a `cascade` option. But `drop owned ` will take care of all that for you. –  Jan 26 '14 at 10:11
  • That can lead to same problem since all function are owned from 'postgres' again. I can successfully drop function with 'cascade' from pgAdmin, otherwise can't. Anyway, for deleting user functions I created a new question. Feel free to join :) and thank you for helping. – Wine Too Jan 26 '14 at 10:26
  • 1
    Again: why do you create your own functions as the superuser? You should create a regular user that owns all of that. –  Jan 26 '14 at 10:28
  • I want to drop triggers fetching from this query * select event_object_schema as table_schema,event_object_table as table_name,trigger_schema,trigger_name,string_agg(event_manipulation, ',') as event,action_timing as activation, action_condition as condition,action_statement as definition from information_schema.triggers where event_object_table like 'his_%' and trigger_name ='set_timestamp' group by 1,2,3,4,6,7,8 order by table_schema,table_name – not-a-bug Aug 19 '20 at 07:28
2

You can "generate" your DROP statement using a SELECT statement. For example:

SELECT 'DROP TABLE "' + table_name + '"' 
FROM information_schema.tables
WHERE table_name LIKE '[your_prefix_here]%'

(Replace '[your_prefix_here]%' with your conditions and wild cards)

hackmaxed
  • 41
  • 2