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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
