I have two tables software.orders and software.products.
I created two views.
CREATE OR REPLACE VIEW software.o1 AS
SELECT orders.orderid, orders.productid, orders.amount
FROM software.orders;
CREATE OR REPLACE VIEW software.o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname
FROM software.o1
JOIN software.products ON o1.productid = products.productid;
Now I take backup for plain type to generate a sql file.
In that file pg_dump shows the view definitions as follows.
CREATE VIEW o1 AS
SELECT orders.orderid, orders.productid, orders.amount FROM orders;
CREATE VIEW o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname FROM (o1 JOIN products ON ((o1.productid = products.productid)));
If I changed view o2 like this.
CREATE OR REPLACE VIEW software.o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname
FROM software.o1
JOIN core.products ON o1.productid = products.productid;
and again generated sql script from pg_dump then it still removes the schema name occurrences of the same schema only where the object resides, from the view definition.
CREATE VIEW o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname FROM (o1 JOIN core.products ON ((o1.productid = products.productid)));
It keeps the schema name 'core' as it is but removes 'software'.
So it makes difficult porting the database to other RDBMS systems and also to manage updates to customer installations. Having few hundred views and few hundred functions makes it more difficult. So it will be better not to remove any schema name when it is explicitly defined.
Regards,
C P Kulkarni
