It seems a pretty simple problem but I can't find an answer to it! How can you delete views in bulk from the postgreSQL console? I have got 10,000 views that I made just to test something and now I can't get rid of them!
0
Add a comment |
6 Answers
you can select the views from the meta tables, like this, (the actual select may differ if you use older version, see here e.g. http://www.alberton.info/postgresql_meta_info.html)
SELECT 'DROP VIEW ' || table_name || ';' FROM information_schema.views WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name !~ '^pg_'; So you fix this select according your actual version, run it, save the results into a .sql file, and run the .sql file.
3 Comments
samach
after executing...hw do i write this to an sql file? copy paste?
bpgergo
psql - save results of command to a file stackoverflow.com/questions/5331320/…
200_success
If some of views reference other views, you may need to do
'DROP VIEW ' || table_name || ' CASCADE;'I add this answer which references a comment by @greg which in turn references an answer, which seems to be gone, from a user named justbob. This comment helped me a lot but might easily be overlooked by others.
Although it doesn't answer samachs question, because his views were already made, it might help others like me who found this question after a google search for a way to delete all views in a bulk operation.
I have a few views which are dependent on another and are generated by an ever-evolving script. So there will be some views added or removed from the script. To limit the places where I have to make changes when adding or removing views I want to drop them all at the beginning of the script before I create them again.
The solution I use now is to create all views in a separate schema which holds nothing but the views.
At the beginning of my script, I have the following statements before creating my views:
DROP SCHEMA IF EXISTS dbview_schema CASCADE; CREATE SCHEMA dbview_schema; After that, I recreate all my views in their own schema:
CREATE VIEW dbview_schema.my_view AS SELECT * FROM my_table ORDER BY my_table.my_column; 
4 Comments
LaVache
This is a really nice & simple solution. Wish I'd thought of it a year or two ago. :) I'm going to set things up this way next time. Much cleaner separate the views in general too.
zhihong
thanks for sharing this idea, also think it is more effective and clear solution!
Saffie
I have exactly the same usecase and I really like your solution.
Tyson
I feel "enlightened" by this answer, it's an elegant solution to the problem of consistently managing view schema. In the context of application schema migrations, it may even be appropriate to have separate view schema for each deployed version of the application using this technique as the basis for managing the different logic required by different versions of the application.
Test this out and see if it works. I'm pulling this out of memory so there may be some syntax issues.
BEGIN TRANSACTION; DO $$DECLARE r record; DECLARE s TEXT; BEGIN FOR r IN select table_schema,table_name from information_schema.views where table_schema = 'public' LOOP s := 'DROP VIEW ' || quote_ident(r.table_schema) || '.' || quote_ident(r.table_name) || ';'; EXECUTE s; RAISE NOTICE 's = % ',s; END LOOP; END$$; ROLLBACK TRANSACTION; 11 Comments
Kuberchaun
The schema name will be what ever one the views are in.
Kuberchaun
FYI this DO statement syntax is only in 9.x+.
greg
I think justbob has the right answer. In order not to have to do that everytime, just creating all your views in a separate
SCHEMA and drop this schema after use would do the job in an easier way.samach
Thanks! im a bit new to this n dont know how to execute functions. should i write this function in the console? my schema for the views is pg_temp_3
Kuberchaun
The example I gave uses an anonymous code block, more for ad-hoc use. You can run it as is without putting it in function. Just log into your database via psql or pgadmin and paste the statement in the console(psql) or gui(pgadmin) and run it. If you want to use a function read this postgresql.org/docs/9.1/static/sql-syntax-calling-funcs.html.
|
DROP ALL VIEW (2022/03/12 version)
shorter version command:
SELECT 'DROP VIEW ' || string_agg (table_name, ', ') || ' cascade;' FROM information_schema.views WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name !~ '^pg_'; output
DROP VIEW v_mytable, v_my_view cascade; DROP ALL MATERIALIZED VIEW
SELECT 'DROP MATERIALIZED VIEW ' || string_agg(oid::regclass::text, ', ') || ' cascade;' FROM pg_class WHERE relkind = 'm'; 
Comments
When I drop particular views first I get a backup with the script below;
WITH dump_options AS ( -- feel free to add any dump options SELECT '--file=/tmp/create_views.sql --host=... my_db_name' AS dump_options ) SELECT format(e'pg_dump --schema-only --format=plain \\\n%s \\\n%s', string_agg( format('--table="%s.%s"', v.schemaname, v.viewname), e' \\\n' ), d.dump_options ) create_views FROM pg_views v, dump_options d WHERE v.schemaname NOT IN ( 'pg_catalog', 'information_schema', 'any_other_schema' ) AND v.viewname NOT IN ( 'pg_stat_statements' ) AND (v.schemaname, v.viewname) NOT IN ( ('a_particular_schema', 'a_particular_view') ) -- AND ... GROUP BY d.dump_options The script creates a dump command which will get backup of all views with owners and privileges when we run it;
pg_dump --schema-only --format=plain \ --table="a_schema.a_view" \ --table="another_schema.another_view" \ ... \ --file=/tmp/create_views.sql --host=... my_db_name Finally, I drop views with the command below (psql recommended);
SELECT format('DROP VIEW IF EXISTS %I.%I;', v.schemaname, v.viewname) drop_views FROM pg_views v WHERE -- the very same where clause used above v.schemaname NOT IN ( 'pg_catalog', 'information_schema', 'any_other_schema' ) AND v.viewname NOT IN ( 'pg_stat_statements' ) AND (v.schemaname, v.viewname) NOT IN ( ('a_particular_schema', 'a_particular_view') ) -- AND ... ; -- \gexec 
Comments
Here's a single statement using a loop. It will also cascade and only drop view if it exists. You can edit where clause to get the right views.
DO $$ DECLARE schema_record RECORD; drop_view_query TEXT; execute_query TEXT; BEGIN FOR schema_record IN SELECT table_schema, table_name FROM information_schema.views WHERE table_schema = '<your table schema>' LOOP -- Create drop view query drop_view_query:= 'DROP VIEW IF EXISTS %I.%I CASCADE'; execute_query:= format(drop_view_query, schema_record.table_schema, schema_record.table_name); -- Execute query RAISE NOTICE 'Attempting to execute: %', execute_query; EXECUTE execute_query; END LOOP; END $$; 