Обсуждение: Suppressing public. in tables names

A while back (January) I asked a question about using Borlands
BDE to access a postgres database via ODBC. In Postgres 7.3, the
table names returned to the application always have the format
'public.tablename'.

Using postgres 7.2, the table name returned to the application is
simply 'tablename'.

What I'd like to know is that is there / could there be a setting in the
odbc driver to allow the suppression of the 'public.' schema when
returning a table name? Possibly an option to ignore schemas
entirely or make them blank?

I've tried setting the search_path to just 'public' & blank with no
success.

If this can't be sorted or worked around it looks like I'm gonna be
stuck on 7.2 forever....

Is there any hope???????

                       -------------------------------------
Paul Cochrane         |       Tayside Orthopaedic &         |
                      | Rehabilitation Technology Centre    |
=============         |-------------------------------------|
                      | Ninewells Hospital & Med. School    |
                      | Dundee, Scotland, UK.               |
                      | DD1 9SY.                            |
                      | Phone:  +44 1382 496284             |
                      | Fax:    +44 1382 496322             |
                      | E-Mail: paul@tortc.tuht.scot.nhs.uk |
                       -------------------------------------

In article <3F55D2A7.14621.1A007BC0@localhost>,
paul@tortc.tuht.scot.nhs.uk says...
> A while back (January) I asked a question about using Borlands
> BDE to access a postgres database via ODBC. In Postgres 7.3, the
> table names returned to the application always have the format
> 'public.tablename'.
>
> Using postgres 7.2, the table name returned to the application is
> simply 'tablename'.
>
> What I'd like to know is that is there / could there be a setting in the
> odbc driver to allow the suppression of the 'public.' schema when
> returning a table name? Possibly an option to ignore schemas
> entirely or make them blank?
>
> I've tried setting the search_path to just 'public' & blank with no
> success.
>
> If this can't be sorted or worked around it looks like I'm gonna be
> stuck on 7.2 forever....
>
> Is there any hope???????

What is the problem?

When I run my queries with the existing table names they work exactly as
they did before, without having to change table names to have "public."
in front of them.

> -----Original Message-----
> From: Paul Cochrane [mailto:paul@tortc.tuht.scot.nhs.uk]
> Sent: 03 September 2003 11:39
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Suppressing public. in tables names
>
>
> A while back (January) I asked a question about using Borlands
> BDE to access a postgres database via ODBC. In Postgres 7.3, the
> table names returned to the application always have the format
> 'public.tablename'.
>
> Using postgres 7.2, the table name returned to the application is
> simply 'tablename'.
>
> What I'd like to know is that is there / could there be a
> setting in the
> odbc driver to allow the suppression of the 'public.' schema when
> returning a table name? Possibly an option to ignore schemas
> entirely or make them blank?

To do so would be very difficult because the current setting of the
search_path will determine what schemas are automatically searched. If
we remove the public. prefix, then tweaking the search path may well
break the application. Conversely, trying to keep track of the search
path and using/removing the search path based on it's setting would
require parsing of all SQL statements issued by the client.

> I've tried setting the search_path to just 'public' & blank with no
> success.

Adjusting the search_path will not stop the ODBC driver returning the
fully qualified name.

> If this can't be sorted or worked around it looks like I'm gonna be
> stuck on 7.2 forever....
>
> Is there any hope???????

Probably! Why is this a problem for you? If you have existing apps that
reference tables without the prefix, then it should be no problem as
long as the search_path is set correctly (which it is by default). The
only other problem I can invisage is if your app for some reason doesn't
like the driver returning fully qualified names, but I can't imagine why
that would be a problem (iirc you would get the same behaviour from the
SQL Server driver for example eg. dbo.tablename).

Regards, Dave.