using the schema in postbooks

On Friday 07 August 2009 6:42:07 am John wrote:

Hi,
There is an accounting system called postbooks that uses Postgres for the
backend. I just downloaded the program yesterday. What is interesting is
within one database there are two schemas (api and public). The 'api'
schema is a bunch of views. The interesting part is if you update a view
in the 'api' it updates a table in the 'public' schema. Could someone
explain how that works? I was not aware that within a databases that the
schema's could talk to each other.

I looked in the doc's (that I have) but did not find an entry that
describes doing anything similar.

Johnf

From:
http://www.postgresql.org/docs/8.4/interactive/sql-createschema.html

"A schema is essentially a namespace: it contains named objects (tables, data
types, functions, and operators) whose names can duplicate those of other
objects existing in other schemas. Named objects are accessed either
by "qualifying" their names with the schema name as a prefix, or by setting a
search path that includes the desired schema(s). A CREATE command specifying an
unqualified object name creates the object in the current schema (the one at
the front of the search path, which can be determined with the function
current_schema). "

--
Adrian Klaver
aklaver@comcast.net

On Fri, Aug 7, 2009 at 9:52 AM, Adrian Klaver <aklaver@comcast.net> wrote:

On Friday 07 August 2009 6:42:07 am John wrote:

Hi,
There is an accounting system called postbooks that uses Postgres for the
backend. I just downloaded the program yesterday. What is interesting

is

within one database there are two schemas (api and public). The 'api'
schema is a bunch of views. The interesting part is if you update a view
in the 'api' it updates a table in the 'public' schema. Could someone
explain how that works? I was not aware that within a databases that the
schema's could talk to each other.

I looked in the doc's (that I have) but did not find an entry that
describes doing anything similar.

Johnf

From:
http://www.postgresql.org/docs/8.4/interactive/sql-createschema.html

It's very simple, you can update something anywhere you have permissions:

insert into api.table....

insert into public.table....

Or by using search_path, which works like the $PATH or %path% environment
variables on linux or windows. It's just a search list of schemas to use.

If my search path was:
public, api

and I type:

create table test (id int);

Then I will have a table called public.test

If my search_path was:
api, public

and I type:

create table test (id int);

Then I will have a table called api

etc...

--Scott

On Friday 07 August 2009 06:56:22 am Scott Mead wrote:

On Fri, Aug 7, 2009 at 9:52 AM, Adrian Klaver <aklaver@comcast.net> wrote:

On Friday 07 August 2009 6:42:07 am John wrote:

Hi,
There is an accounting system called postbooks that uses Postgres for
the backend. I just downloaded the program yesterday. What is
interesting

is

within one database there are two schemas (api and public). The 'api'
schema is a bunch of views. The interesting part is if you update a
view in the 'api' it updates a table in the 'public' schema. Could
someone explain how that works? I was not aware that within a
databases that the schema's could talk to each other.

I looked in the doc's (that I have) but did not find an entry that
describes doing anything similar.

Johnf

From:
http://www.postgresql.org/docs/8.4/interactive/sql-createschema.html

It's very simple, you can update something anywhere you have permissions:

insert into api.table....

insert into public.table....

Or by using search_path, which works like the $PATH or %path% environment
variables on linux or windows. It's just a search list of schemas to use.

If my search path was:
public, api

and I type:

create table test (id int);

Then I will have a table called public.test

If my search_path was:
api, public

and I type:

create table test (id int);

Then I will have a table called api

etc...

--Scott

Interesting where is the search path set? Better how is it set?

Johnf

On Friday 07 August 2009 07:27:28 am John wrote:

On Friday 07 August 2009 06:56:22 am Scott Mead wrote:

On Fri, Aug 7, 2009 at 9:52 AM, Adrian Klaver <aklaver@comcast.net> wrote:

On Friday 07 August 2009 6:42:07 am John wrote:

Hi,
There is an accounting system called postbooks that uses Postgres for
the backend. I just downloaded the program yesterday. What is
interesting

is

within one database there are two schemas (api and public). The
'api' schema is a bunch of views. The interesting part is if you
update a view in the 'api' it updates a table in the 'public' schema.
Could someone explain how that works? I was not aware that within a
databases that the schema's could talk to each other.

I looked in the doc's (that I have) but did not find an entry that
describes doing anything similar.

Johnf

From:
http://www.postgresql.org/docs/8.4/interactive/sql-createschema.html

It's very simple, you can update something anywhere you have
permissions:

insert into api.table....

insert into public.table....

Or by using search_path, which works like the $PATH or %path% environment
variables on linux or windows. It's just a search list of schemas to
use.

If my search path was:
public, api

and I type:

create table test (id int);

Then I will have a table called public.test

If my search_path was:
api, public

and I type:

create table test (id int);

Then I will have a table called api

etc...

--Scott

Interesting where is the search path set? Better how is it set?

Johnf

Sorry I figured out how it works.

Thanks to all,

Johnf