The following bug has been logged on the website:
Bug reference: 6660
Logged by: C P Kulkarni
Email address: shreeseva.it@gmail.com
PostgreSQL version: 9.1.3
Operating system: Fedora 16
Description:=20=20=20=20=20=20=20=20
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.=20
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.=20
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 =3D 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 =3D 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 =3D 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 =3D 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.
