Hello,

I have several databases. The objects are stored in
the public schema of each database.

I would like to move them to different schemas in one single database.

In the softwares that use these databases, the sql statements
don't mention the schemas.
I found out that it should be possible to set the search_path variable
in order to access the tables, views (etc...) without rewriting all
the sql statements.

I found this link :
http://archives.postgresql.org/pgsql...1/msg00013.php
I would be perfect for me, but unfortunately it doesn't work.

If I append ";A6=set search_path to my_schema,public;"
to my connection string, I get an error message telling
me the relation doesn't exist (so I assume the search_path
is not properly set).
I tried many ways to do it (replaced A6 with ConnSettings, for instance)
but none seems to work.

Does anyone know how i can achieve this?

Tanguy <tanguy (AT) nowhere (DOT) fr> wrote:
Do you have different users? If yes, you can set the search_path per
user, using "alter user foo set search_path= ..." once for all.

Other solution, but untested (i don't know much about ODBC, but it works
for connectionis with PHP for instance): set the search_path as a normal
sql-statement, for instance: "set search_path=...; select * from foo"
once per session.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

Tanguy <tanguy (AT) nowhere (DOT) fr> wrote:
I did something similar some time ago (using 'A6'), and I found I needed
to escape some of the characters in the SQL statement, as follows:

space -> +
underscore -> %5f
comma -> %2c

Then it worked for me.

-M-

On Tue, 08 Dec 2009 15:22:32 +0100, Tanguy wrote:

You can set path in postgresql.conf. There is a parameter you can
uncomment if you want to set it to something other than default:

mgogala@nyclapwxp2622:~$ grep search_path /etc/postgresql/8.4/main/
postgresql.conf
#search_path = '"$user",public' # schema names
mgogala@nyclapwxp2622:~$

I am running Ubuntu 9.04 with PostgreSQL 8.4.1:

mgogala@nyclapwxp2622:~$ uname -a
Linux nyclapwxp2622 2.6.28-17-generic #58-Ubuntu SMP Tue Dec 1 18:57:07
UTC 2009 i686 GNU/Linux

gogala@nyclapwxp2622:~$ psql
psql (8.4.1)
Type "help" for help.

mgogala=# select version();

version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.1 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Ubuntu 4.3.3-5ubuntu4) 4.3.3, 32-bit
(1 row)

Your question should include your platform and version, too. That will
will make the process of searching the solutions for your problem easier.

--
http://mgogala.byethost5.com

Andreas Kretschmer a écrit :


Hello,

That's not exactly what i want to do, but if it works it could be an
acceptable workaround.
I will just have to set a different users for each software.

....Oops, i just tried, it doesn't work either.
(I'll check again, i 'm starting to think i'm doing something wrong in
my software)
Thanks for the tip anyway.



I had tried this one. It works ok in psql, but not in the software

Mladen Gogala a écrit :

Hello,

That might be a solution, but i thing this is a global setting ?



Yes, you're right. I forgot to mention this.

I run postgresql on a redhat server, kernel version 2.6.18
Postgresql version :
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.6 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

I compiled postgresql from the source code.

Matthew Woodcraft a écrit :

Thanks for your answer, i'll try this.

Where did you set this A6 parameter ?
I found you can do it in several places (in odbc.ini, in the connection
string in the software...)

Tanguy <tanguy (AT) nowhere (DOT) fr> wrote:
Exactly.



Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Tanguy wrote:
Yes, that is a global setting.

Yours,
Laurenz Albe

Andreas Kretschmer a écrit :

Ok, it works.

I created a user for the database, I set
search_path = '"$user",public'
in the postgresql.conf file.

I realized I had not properly granted the access to the user.
Once it was done, it worked fine.


Thanks to everyone who helped me!