pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: working with schema
5+ messages / 3 participants
[nested] [flat]
* Re: working with schema
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
parent: William Anthony Lim <wei.liang@eudoramail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: wei.liang@eudoramail.com; +Cc: pgsql-sql
>
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* working with schema
@ 2004-05-10 08:10 William Anthony Lim <wei.liang@eudoramail.com>
0 siblings, 1 reply; 5+ messages in thread
From: William Anthony Lim @ 2004-05-10 08:10 UTC (permalink / raw)
To: pgsql-sql
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
Need a new email address that people can remember
Check out the new EudoraMail at
http://www.eudoramail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: working with schema
@ 2004-05-12 10:19 William Anthony Lim <wei.liang@eudoramail.com>
0 siblings, 1 reply; 5+ messages in thread
From: William Anthony Lim @ 2004-05-12 10:19 UTC (permalink / raw)
To: pgsql-sql
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?
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?
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: working with schema
@ 2004-05-12 13:59 scott.marlowe <scott.marlowe@ihs.com>
parent: William Anthony Lim <wei.liang@eudoramail.com>
0 siblings, 0 replies; 5+ messages in thread
From: scott.marlowe @ 2004-05-12 13:59 UTC (permalink / raw)
To: William Anthony Lim <wei.liang@eudoramail.com>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: working with schema
@ 2004-05-13 05:25 William Anthony Lim <wei.liang@eudoramail.com>
0 siblings, 0 replies; 5+ messages in thread
From: William Anthony Lim @ 2004-05-13 05:25 UTC (permalink / raw)
To: pgsql-sql
There is something still annoying me Scott..
eg: I want to have function that take a string argument that indicates which schema i want to use.
create function testf(varchar)
returns ...
..
..
..
..
My question is how to use the argument in the function, maybe looks like:
select * from $1.test
or
set search_path to $1
select * from test
or maybe I defined a string variable to hold it,
workschema='D200402'
select * from workschema.test
Do they work?
Thanks,
William
>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
>
Need a new email address that people can remember
Check out the new EudoraMail at
http://www.eudoramail.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-05-13 05:25 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-10 08:10 working with schema William Anthony Lim <wei.liang@eudoramail.com>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2004-05-12 10:19 Re: working with schema William Anthony Lim <wei.liang@eudoramail.com>
2004-05-12 13:59 ` scott.marlowe <scott.marlowe@ihs.com>
2004-05-13 05:25 Re: working with schema William Anthony Lim <wei.liang@eudoramail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox