Search Postgresql Archives

Re: losing schema name in pg_dump

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux