Re: [GENERAL] How to drop column from interrelated views

I've got some functions on my own :)
If you prefer to do it from SQL level I've got two functions prepared which
first secure then restore all missing views definitions.


-- secure all views
DO $$
BEGIN
--drop schema migration cascade
  CREATE SCHEMA migration;

  CREATE TABLE migration.views AS
    SELECT
      table_schema,
      table_name,
      view_definition
    FROM INFORMATION_SCHEMA.views
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

  CREATE TABLE migration.view_count AS
    SELECT
      count(*),
      'before' :: TEXT AS desc
    FROM INFORMATION_SCHEMA.views
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

END;
$$;

/*
 HERE DO YOUR EVIL DROP CASCADE
 YOUR VIEWS DEFINITIONS ARE SAFE IN MIGRATION SCHEMA
 REMEMBER YOU BACKED UP ONLY VIEWS DEFINITIONS - NO TABLES, RULES OR DATA
 */

-- restore all dropped views / only not existing views
DO $$

DECLARE
  l_string TEXT;
BEGIN

  FOR l_string IN SELECT 'CREATE VIEW ' || table_schema || '.' || table_name
|| ' AS '
                         || view_definition
                  FROM migration.views
  LOOP
    BEGIN
      EXECUTE l_string;
      EXCEPTION WHEN OTHERS THEN
      -- do nothing
    END;
  END LOOP;

  IF ((SELECT count
       FROM migration.view_count) = (SELECT count(*)
                                     FROM INFORMATION_SCHEMA.views
                                     WHERE table_schema NOT IN
('pg_catalog', 'information_schema')))
  THEN
    RAISE NOTICE 'Migration successful';
  ELSE
    RAISE NOTICE 'Something went wrong';
  END IF;

END;
$$;



If migration was successful you can drop schema migration.





--
View this message in context:
http://www.postgresql-archive.org/How-to-drop-column-from-interrelated-views-tp5970484p5970518.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.