Re: 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.