Thread: selecting other schema's as the public one from php

Hello,
I've created a new schema in my database but i can't select anything from the tables into this schema, selecting from the public schema gives no trouble.
I've already tried using the postgres user so i think nothing can be wrong with privileges. Any suggestions?

Michael cochez

---

Shape Yahoo! in your own image. Join our Network Research Panel today!

Michael Cochez wrote:
> Hello,
> I've created a new schema in my database but i can't select anything
> from the tables into this schema, selecting from the public schema gives
> no trouble.
> I've already tried using the postgres user so i think nothing can be
> wrong with privileges. Any suggestions?

Try this:

set search_path to NEWSCHEMA,public;

where NEWSCHEMA is the name of your new schema.  Do this BEFORE doing
anything else with the connection.  This will tell postgres to look in
NEWSCHEMA as well as the public schema.  You will need to do this with
every new connection to the database.

Sean

Thanks for you quick reply, meanwhile I've found some kind of solution (but I'm not realy shure about what I'm doing actualy)
I use "SELECT * FROM schemaname.tablename" and so I can select from the table I created, this seems to work too when creating views to go beyond the boundaries of the current schema.
The search_path change worked to, but what are you doing when you type this?
Are you enlarging some kind of local scope with the objects inside that schema or something?
Michael cochez

Sean Davis <sdavis2@mail.nih.gov> wrote:

Michael Cochez wrote:
> Hello,
> I've created a new schema in my database but i can't select anything
> from the tables into this schema, selecting from the public schema gives
> no trouble.
> I've already tried using the postgres user so i think nothing can be
> wrong with privileges. Any suggestions?

Try this:

set search_path to NEWSCHEMA,public;

where NEWSCHEMA is the name of your new schema. Do this BEFORE doing
anything else with the connection. This will tell postgres to look in
NEWSCHEMA as well as the public schema. You will need to do this with
every new connection to the database.

Sean

---

Looking for a deal? Find great prices on flights and hotels with Yahoo! FareChase.

Michael Cochez wrote:
> Thanks for you quick reply, meanwhile I've found some kind of solution
> (but I'm not realy shure about what I'm doing actualy)
> I use "SELECT * FROM schemaname.tablename" and so I can select from the
> table I created, this seems to work too when creating views to go beyond
> the boundaries of the current schema.

What you are doing above is perfectly acceptable and is preferred over
what I showed.

> The search_path change worked to, but what are you doing when you type this?
> Are you enlarging some kind of local scope with the objects inside that
> schema or something?

Yep.  Basically, you expand the scope when looking for objects to be
first found in NEWSCHEMA and then in public.  Obviously, it is possible
to mask tables in public by having the same table name in NEWSCHEMA.
Hence, you probably want to go with the solution you give (and not
mine).  Sorry for putting out misinformation.

Sean