How to add columns to view with dependencies
Seems like a simple question, but I’ve never found a good answer to this and similar issues.
I would think it was safe to let me add columns to a view on which other views depend, but Postgres won’t let me.
I can imagine ways of sort-of dealing with this. I might maintain a SQL file with views to create in a suitable order, Then I could drop all views, edit the definition of one, then run the file, but this is awfully tedious.
What is best practice in this situation?
On Sun, Apr 16, 2017 at 08:02:54PM -0700, Guyren Howe wrote:
Seems like a simple question, but I’ve never found a good answer to this and similar issues.
I would think it was safe to let me add columns to a view on which other views depend, but Postgres won’t let me.
I can imagine ways of sort-of dealing with this. I might maintain a SQL file with views to create in a suitable order, Then I could drop all views, edit the definition of one, then run the file, but this is awfully tedious.
What is best practice in this situation?
If you're not re-ordering existing columns, you can use CREATE OR REPLACE VIEW
Justin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Guyren Howe <guyren@gmail.com> writes:
Seems like a simple question, but I’ve never found a good answer to this and similar issues.
I would think it was safe to let me add columns to a view on which other views depend, but Postgres won’t let me.
I can imagine ways of sort-of dealing with this. I might maintain a SQL file with views to create in a suitable order, Then I could drop all views, edit the definition of one, then run the file, but this is awfully tedious.
What is best practice in this situation?
Hm ... all currently-supported versions of Postgres will allow, eg,
regression=# create table t1 (f1 int, f2 int, f3 int);
CREATE TABLE
regression=# create view v1 as select f1 from t1;
CREATE VIEW
regression=# create or replace view v1 as select f1, f2 from t1;
CREATE VIEW
regression=# create view v2 as select * from v1;
CREATE VIEW
regression=# create or replace view v1 as select f1, f2, f3 from t1;
CREATE VIEW
So I think your options are (1) explain what you're really doing,
or (2) update.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Sun, Apr 16, 2017 at 08:02:54PM -0700, Guyren Howe wrote:
I can imagine ways of sort-of dealing with this. I might
maintain a SQL file with views to create in a suitable order,
Then I could drop all views, edit the definition of one, then
run the file, but this is awfully tedious.
This, kept under version control, seems best practice,
regardless of what PG supports making the above easier.
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general