Thread: losing schema name in pg_dump

Hi all,

Many times I have to dump all objects from a schema (single schema holding only functions and views) in plain text format. It is found that pg_dump includes a set search_path statement at the beginning and drops all occurrences of the schema name (to which dumped object belongs) from function/view bodies even if they are explicitly defined while creating functions/views.

Functions or view definition from information_schema tables shows the explicitly defined schema names but pg_dump does not shows them. Is there any switch to change this behaviour? Or pg_dump has this by default and we can not change this?
This makes it problematic to maintain the software available for another RDBMS as for most other RDBMS we have to explicitly define schema names always.

Thanks,

C P Kulkarni

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