Re: BUG #6660: losing schema name in pg_dump

Thanks for your reply.
I know that pg_dump is not intended to provide SQL script to be working in
many other DBMS. But even if I have to dump all functions/views (tables and
other objects are not changed a lot like functions/views due to changes in
business rules or behaviour) from a certain schema with explicit schema
names and if I have objects with same name in other schemas which are also
part of the search_path then it creates a problem.
I didn't understand when user have defined schema names explicitly in
definition of the objects and most database developers insist on writing
schema names explicitly, why pg_dump is not outputting it as it is. I know
that using SCM is better but many times such object definitions are
directly transferred to clients without SCM and it can create problems when
using same object names in multiple schemas.

Now I got details about pg_get_functiondef(oid) from pg_catalog which
outputs the definitions of functions as it is. So thanks a lot. It will be
still helpful not omit schema name when explicitly defined. You are also
requested to give more details about why t is more convenient to omit
schema names?

Regards,
C P Kulkarni

On Tue, May 22, 2012 at 7:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> shreeseva(dot)it(at)gmail(dot)com writes:
> > 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.
>
> This is not a bug. It's intentional behavior and is convenient more
> often than not.
>
> > 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.
>
> This claim seems quite baseless. You certainly haven't offered any
> actual evidence of a situation where omitting unnecessary schema names
> is problematic.
>
> More generally, though, pg_dump has never attempted to promise that its
> output can be loaded into other DBMSes without pain, and it would be
> practically impossible to make such a promise; there are too many subtle
> differences in the various dialects of SQL. If you have SQL that is
> intended to work in multiple DBMSes, you would be best advised to
> maintain it as text files (perhaps in an SCM) rather than hope that
> pg_dump's output will still be useful for the other systems.
>
> regards, tom lane
>

--
Chaitanya P. Kulkarni

Copyright. All rights are reserved for any object, text, ideas, images and
any other things.
-----------------------------------------------------------------------
Keep your Environment clean and green.
--------------------------xox----------------------------------------