Thread: set the default schema

Hi,

I do not own my database. in fact, it is stored on my web hoster server.
therefore i would like to know how can i setup the default SCHEMA in order to no have to write in all my functions, queries or procedures, the following thing :

schema_name.function_name(parameters,...)

e.g : if my schema is named 'immense' and my function is named 'sp001'

immense.sp001(parameter1, parameter2)
...

thanks a lot


Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

am  Mon, dem 20.11.2006, um 19:59:17 +0100 mailte Alain Roger folgendes:
> Hi,
>
> I do not own my database. in fact, it is stored on my web hoster server.
> therefore i would like to know how can i setup the default SCHEMA in order to
> no have to write in all my functions, queries or procedures, the following
> thing :

alter user <your_user> set search_path=<insert your favorite search_path here);


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On Mon, 2006-11-20 at 12:59, Alain Roger wrote:
> Hi,
>
> I do not own my database. in fact, it is stored on my web hoster
> server.

You must be from oracle land.  Welcome to PostgreSQL land.  It's a
little different.   (as visions of alter session set current_schema swim
through my head).  In PostgreSQL, schemas are NOT tied to users.  They
are a free form thing.  Oracle's a little easier, PostgreSQL is a little
more versatile.

You can do it a few ways.

Let's say you create a new schema test:

create schema test;

and you'd like people who haven't set any parameters to "Drop into" it
when they connect:

alter database test set search_path='test';

poof.  joe user will now drop on top of the test schema.  But wait,
there's more!  you can have more than one.  So, you can do this:

alter database test set search_path='test','public';

Add as many other schemas as you'd like.

Then, as an added bonus, you, the user, can override all that:

alter user smarlowe set search_path = 'abc','public';

Now, all the things that smarlowe creates will go into abc, but using a
table name will search first the abc schema for a match, then the public
schema.

Pretty neat stuff.  Read more here:

http://www.postgresql.org/docs/8.1/static/ddl-schemas.html

tres cool.