Re: What's the CURRENT schema ?

Hiroshi Inoue wrote:
>
> > We can't do that. Accordingly to the SQL if you are user HIROSHI
> > and write "SELECT * FROM a;" the table is actually "HIROSHI.a".
> >
> > This must work for people who are using SQL-schemas in their databases
> > or we would have a non-conforming implementation of SCHEMAS (would make
> > the whole exercise pointless IMO).
>
> Schema name isn't necessarily a user id since SQL-92
> though SQL-86 and SQL-89 had and probably Oracle still
> has the limitation. As far as I see PostgreSQL's schema
> support wouldn't have the limitation. Probably I wouldn't
> create the schema HIROSHI using PostgreSQL. When
> I used Oracle I really disliked the limitation.
>

You misunderstood what I've said. You may have how many schemas
you please. But you will have to refer to their objects specifying
the schema name explicitly. The only cases where you can omit the
schema name are (accordingly to the SQL'99 standard):

1) The statement is part of a CREATE SCHEMA statement that is
creating the object, so the schema being created is assumed
(and that is what you want).

2) Your schema has the same name as your user id, your statement
is not inside a CREATE SCHEMA and it runs on a session with that
authorization id. A schema name equal to the sessuin user id is
assumed (which is what you want in this specific case).

Otherwise you have to specify the schema explicitly.

So, if you name your schema "APPLE", and not HIROSHI, except
for inside the CREATE SCHEMA APPLE statement elements, you will
have to keep refering to tables with the "APPLE." prefix.

PostgreSQL will be smarter and try to relax 2) for you, looking
for the table in a public schema as well (if one exists), so old
style (non-schema) databases can still be used and people who have
schemas with names that are not their user id can save some typing. ;-)

--
Fernando Nasser
Red Hat Canada Ltd. E-Mail: fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9