Re: [GENERAL] How to drop column from interrelated views - Mailing list pgsql-general
On Sun, Jul 9, 2017 at 9:56 AM, Berend Tober <btober@computer.org> wrote:
Guyren Howe wrote:On Jul 8, 2017, at 16:11 , Berend Tober <btober@computer.org <mailto:btober@computer.org>>
wrote:
Guyren Howe wrote:I’ve a set of interrelated views. I want to drop a column from a table and from all the views
that cascade from it.
I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the
field from the intermediate views because Postgres doesn’t appear to be clever enough to see
that the leafs no longer depend on the column. Or did I just miss one?
In general, this seems like a major weakness expressing a model in Postgres (I get that any
such weakness derives from SQL; that doesn’t stop me wanting a solution).
Thoughts? Comments?
This usually involves a pg_dump in the custom format, editing the list file, creating a script
with pg_restore.
I described a way I have had success with it at one point at
https://www.postgresql.org/message-id/55C3F0B4.5010600%40com puter.org
I was wondering if I changed up all the things that interrelate in a transaction, whether that
would bundle them up so they’re all correct afterward. I was hoping so.
Well, nothing beats empirical evidence ... set up a test case and try it!
You definitely want to do it in a transaction anyway, so that if you get it wrong the first few times and have to iterate, the data base rolls back to where you started.
Note the method suggested in the earlier link appears to have a error. Step 4 should be
pg_restore -c -1 -L mydatabase.list mydatabase.dump > sql
The lower case "c" flag will include DROP statements for the views. The "1" will wrap in a transaction, like you want.
BTW, please generally use the "reply-all" functionality of your email client when interacting with this list ... the server puts the list alias in the CC line, so you have to reply all to keep the conversation publicly available for others to learn from.
-- B
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Another alternative is to simply extract all the view defs with the column name you want to drop,
edit the viewdef to remove the columns and then use that to redefine the views BEFORE
dropping the column from the table.
IE: in the following query, replace <COLUMN_NAME> with the name of the column to be dropped.
1. Make a SQL pg_dump of the database before proceeding
2.
\o edit_views.sql
SELECT 'CREATE OR REPLACE VIEW '
|| n.nspname || '.' || c.relname || ' AS '
|| pg_get_viewdef(c.oid, true)
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'v'
AND n.nspname NOT LIKE 'pg_%'
AND n.nspname NOT LIKE 'sql_%'
AND n.nspname NOT LIKE 'information%'
AND a.attname = '<COLUMN_NAME>'
ORDER BY 1;
|| n.nspname || '.' || c.relname || ' AS '
|| pg_get_viewdef(c.oid, true)
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_namespace n ON (n.oid = c.relnamespace)
WHERE c.relkind = 'v'
AND n.nspname NOT LIKE 'pg_%'
AND n.nspname NOT LIKE 'sql_%'
AND n.nspname NOT LIKE 'information%'
AND a.attname = '<COLUMN_NAME>'
ORDER BY 1;
3. Edit edit_views.sql to remove all occurrences of the column being dropped
4.psql <your_db_name> < edit_views.sql
5. Then drop the column from the table.
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: