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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
