On Wed, 12 May 2004, William Anthony Lim wrote:
> Christoph,
>
> First, is it safe for multi user? I mean maybe first user need working with D200402,
> second one need with D200403, if I do this in first user connection:
>
> SET search_path to D200402 ;
>
> does it affect to the second user search path?
No, search paths are session vars.
> Second, I want it dinamic. So, if I want to using D200402, I just need to pass
> 'D200402' string in the argument of the function. Got my point?
You should be able to do it with dot notation:
postgres=# create schema a;
CREATE SCHEMA
postgres=# create schema b;
CREATE SCHEMA
postgres=# create table a.test (info text);
CREATE TABLE
postgres=# create table b.test (info text);
CREATE TABLE
postgres=# insert into a.test values ('abc');
INSERT 1400496 1
postgres=# insert into b.test values ('123');
INSERT 1400497 1
-- Now we try to look up the table without setting a search path and no
-- dot notation:
postgres=# select * from test;
ERROR: relation "test" does not exist
ERROR: relation "test" does not exist
-- Now we set the search path, notice the order:
postgres=# set search_path=public,a,b;
SET
postgres=# select * from test;
info
------
abc
(1 row)
-- Reverse the order of a and b
postgres=# set search_path=public,b,a;
SET
postgres=# select * from test;
info
------
123
(1 row)
-- now without a
postgres=# set search_path=public,b;
SET
postgres=# select * from test;
info
------
123
(1 row)
postgres=# set search_path=public,a;
SET
postgres=# select * from test;
info
------
abc
(1 row)
-- Now we use dot notation. first a, then b. Notice that
-- b, which isn't in our search path, works fine.
postgres=# select * from a.test;
info
------
abc
(1 row)
postgres=# select * from b.test;
info
------
123
(1 row)
>
> Thanks anyway,
>
> William
>
> >>
> >> Hi all,
> >>
> >> I'm just experimenting with schema usage. I'm going to use it as a fake
> >> 'multi-database' system. Is Postgresql support coding schema name using string
> >> variable so I can pass it with parameter? I'm give u an example:
> >>
> >> I have schema: D200401,D200402.D200403,D200404, etc.
> >>
> >> I've set my user just like the schema name, so who login with D200401 will be
> >> using D200401 schema. When someone using D200401 schema, they sometime want to
> >> access another schema, so in my thought I can use variable like this:
> >>
> >> sPointer='D200403'
> >>
> >> select * from sPointer.myTable -- Question: How to write it to work properly?
> >>
> >> Thanks
> >>
> >>
> >> William
> >>
> >>
> >SET search_path to D200401 ;
> >SET search_path to D200402 ;
> >...
> >should do the job.
> >
> >Regards, Christoph
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 4: Don't 'kill -9' the postmaster
> >
>
>
>
> Need a new email address that people can remember
> Check out the new EudoraMail at
> http://www.eudoramail.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
