[Date Prev][Date Next] [Thread Prev][Thread Next] [Date Index] [Thread Index]

Re: OT: Calling SQL experts

On Sat, 2005-04-16 at 18:47 +0100, Chris Boot wrote:
> Alan Chandler wrote:
> >Can someone tell me the scope of the various SQL names.  I have googled around 
> >all afternoon looking for a document that defines it and can find none.
> >
> >In particular, I am trying to convert the textpattern package to use a 
> >postgres database rather than the mysql one.
> >
> >One of the characteristics of textpattern is that it allows all table names to 
> >have a (setup defined) prefix so that two instances of textpattern can run in 
> >the same database.  Some of these tables have columns which form indexes.
> >
> >In mysql these are declared implicitly within the CREATE TABLE statement.  For 
> >Postgres I need to explicitly declaire them with a CREATE INDEX statement.  
> >Without knowing the scope of the INDEX name identifier, I can't tell whether 
> >I have to add the prefix on to all index names.
> >
> >Can someone point me at a SQL tutorial that describes to what extent (across 
> >what surrounding concept (database, table, ...) various names have to be 
> >unique
> >
> >TIA
> >  
> >
> Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
> Type:  \copyright for distribution terms
>        \h for help with SQL commands
>        \? for help on internal slash commands
>        \g or terminate with semicolon to execute query
>        \q to quit
> cerberus=# create table foo_table ( id INTEGER );
> cerberus=# create table bar_table ( id INTEGER );
> cerberus=# CREATE INDEX foo_idx ON foo_table (id);
> cerberus=# CREATE INDEX foo_idx ON bar_table (id);
> ERROR:  relation "foo_idx" already exists
> cerberus=# CREATE INDEX bar_idx ON bar_table (id);
> cerberus=#

Note, though, that if you use schemas, lexical scoping is bounded
by the schema.

Ron Johnson, Jr.
Jefferson, LA USA
PGP Key ID 8834C06B I prefer encrypted mail.

"Peace is that brief glorious moment in history when everybody
stands around reloading."

Attachment: signature.asc
Description: This is a digitally signed message part

Reply to: