Dropping all views in postgreSql

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;

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.

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;