schema name in SQL statement.

-------------- Original message ----------------------
From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com>

I have to explicitly specify the schema name to make SQL statement to
work.
Can I set the schema before the query, or set a default schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

--

See search_path in:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html

--
Adrian Klaver
aklaver@comcast.net

On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote:

-------------- Original message ----------------------
From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com>

I have to explicitly specify the schema name to make SQL statement to
work.
Can I set the schema before the query, or set a default schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

--

See search_path in:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html

--
Adrian Klaver
aklaver@comcast.net

I see answers like this all the time. When I review the doc's I still don't
know how to set the "search_path" because there is no example in the doc's.
Do I do something like this:
"select search_path=(public)" or "select search_path="public" . So how is
the search_path set?

--
John Fabiani

On Tue, Aug 19, 2008 at 10:53 PM, johnf <jfabiani@yolo.com> wrote:

On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote:

From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com>

I have to explicitly specify the schema name to make SQL statement to
work.
Can I set the schema before the query, or set a default schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

See search_path in:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html

I see answers like this all the time. When I review the doc's I still don't
know how to set the "search_path" because there is no example in the doc's.
Do I do something like this:
"select search_path=(public)" or "select search_path="public" . So how is
the search_path set?

But there is a link on that page under search_path that points here:

http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html

which has much better info on search_path in it.

On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote:

On Tue, Aug 19, 2008 at 10:53 PM, johnf <jfabiani@yolo.com> wrote:

On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote:

From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com>

I have to explicitly specify the schema name to make SQL statement to
work.
Can I set the schema before the query, or set a default schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

See search_path in:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.htm
l

I see answers like this all the time. When I review the doc's I still
don't know how to set the "search_path" because there is no example in
the doc's. Do I do something like this:
"select search_path=(public)" or "select search_path="public" . So how
is the search_path set?

But there is a link on that page under search_path that points here:

http://www.postgresql.org/docs/8.3/interactive/ddl-schemas.html

which has much better info on search_path in it.

Thanks - I did not note the link.

--
John Fabiani

-----Original Message-----
From: johnf <jfabiani@yolo.com>
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] schema name in SQL statement.
Date: Tue, 19 Aug 2008 22:25:14 -0700

On Tuesday 19 August 2008 10:06:55 pm Scott Marlowe wrote:

On Tue, Aug 19, 2008 at 10:53 PM, johnf <jfabiani@yolo.com> wrote:

On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote:

From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com>

I have to explicitly specify the schema name to make SQL statement to
work.
Can I set the schema before the query, or set a default schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

See search_path in:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.htm
l

I see answers like this all the time. When I review the doc's I still
don't know how to set the "search_path" because there is no example in
the doc's. Do I do something like this:
"select search_path=(public)" or "select search_path="public" . So how
is the search_path set?

set search_path = 'xmxmxmxmxmxm'

On Tuesday 19 August 2008 9:53:11 pm johnf wrote:

On Tuesday 19 August 2008 04:01:55 pm Adrian Klaver wrote:

-------------- Original message ----------------------
From: "Masis, Alexander (US SSA)" <alexander.masis@baesystems.com>

I have to explicitly specify the schema name to make SQL statement to
work.
Can I set the schema before the query, or set a default schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

--

See search_path in:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-client.html

--
Adrian Klaver
aklaver@comcast.net

I see answers like this all the time. When I review the doc's I still
don't know how to set the "search_path" because there is no example in the
doc's. Do I do something like this:
"select search_path=(public)" or "select search_path="public" . So how is
the search_path set?

--
John Fabiani

From postgresql.conf.sample in ~pgsql/share:

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public' # schema names

This is how the initial postgresql.conf is set up when you do an initdb. I
have found the postgresql.conf to be quite well commented.

--
Adrian Klaver
aklaver@comcast.net

On Wed, Aug 20, 2008 at 8:13 AM, Adrian Klaver <aklaver@comcast.net> wrote:

From postgresql.conf.sample in ~pgsql/share:

#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user",public' # schema names

This is how the initial postgresql.conf is set up when you do an initdb. I
have found the postgresql.conf to be quite well commented.

I have a question (not necessarily to you...)
. How come that syntax parses for postgresql.conf, but in psql it doesn't:

set search_path = '"$user",public' ;
ERROR: schema ""$user",public" does not exist

I have to use this:

set search_path = "$user",public ;
SET

--- On Tue, 8/19/08, Masis, Alexander (US SSA) <alexander.masis@baesystems.com> wrote:

From: Masis, Alexander (US SSA) <alexander.masis@baesystems.com>
Subject: [GENERAL] schema name in SQL statement.
To: pgsql-general@postgresql.org
Date: Tuesday, August 19, 2008, 10:52 PM
I have to explicitly specify the schema name to make SQL
statement to
work.
Can I set the schema before the query, or set a default
schema?
My current statement:
"SELECT col FROM schema.table"
I like to be able to use generic SQL statement like:
"SELECT col FROM table"

--
Sent via pgsql-general mailing list
(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

you can change the search path of the schema

SET search_path TO myschema