Re: using the schema in postbooks

[Scott Mead <scott.lists@xxxxxxxxxxxxxxxx>]

On Fri, Aug 7, 2009 at 9:52 AM, Adrian Klaver <aklaver@xxxxxxxxxxx> 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