On Thu, 13 Jan 2005 12:13:35 -0600, Ian Bicking <ianb@...> wrote:
> Jason Stewart wrote:
> > I messed around a bit with the SQL code that finds the keys in pgsql
> > to get 'schema.tablename' to work, but then I ran into problems with
> > the field names retaining the schema information during queries like
> > this: schema.table.field which is invalid for a field name. Only
> > table.field is valid when performing a query. The schema name could be
> > dropped from the table name but would require a messy string trim.
> Is this in Postgres? I know I've seen PG commands like:
> SELECT blah blah blah WHERE
> Where I believe pg_catalog is a schema, and pg_table_is_visible is a
> function, which seems to imply that it's okay to use schema names in a
> where clause.
> If not, I suspect what needs to be done is to alias all the tables in a
> query, e.g.: SELECT blah blah FROM schema1.table1 AS schema1_table1.
> Table aliasing always annoys me, but I guess it's not that big a deal.
> Ian Bicking / ianb@... / http://blog.ianbicking.org
> The SF.Net email is sponsored by: Beat the post-holiday blues
> Get a FREE limited edition SourceForge.net t-shirt from ThinkGeek.
> It's fun and FREE -- well, almost....http://www.thinkgeek.com/sfshirt
> sqlobject-discuss mailing list
I'm coming late to the discussion, but -
In Oracle (and looking at the documentation in SQL92) a schema is
simply a collection of objects.
Strictly speaking you *have* to preface a database object (table,
view, stored procedure, etc.) with it's schema name. Hence you use the
schema name in the from clause or when referring to another object (a
function in Ian's example above) in a where clause.
Different databases have different means for inferring what your
default schema is though. PostgreSQL uses the generic 'Public' schema,
MySQL just implements each schema as it's own database and Oracle only
ever looks in the logged in user's schema.
Oracle does, however, supports synonyms as first class database
objects. So if I'm logged in as andy and refer to a table called
'widgets' in my SQL statement something will be returned if I have a
table, view or stored procedure called 'wibble' or I have a synonym
called 'wibble' which points to a table, view or stored procedure in
somebody else's schema.
So I'd plump for the schema to be an attribute of a SQLObject as Oleg suggests.
From the desk of Andrew J Todd esq