using schemas in postgresql

Question

I have developed an application using postgresql and it works well.

Now I need to create several instances of the same application but I have only one database. So I am thinking about using schemas, so that I can group each instance tables in a different schema.

Now, I wouldn't like to rewrite all my functions and script, thus I am wondering if I can just use some directive to instruct the database to operate on a specific schema. Just to try to make it clearer, do you know when in c++ you do

using namespace std;

so that you can use cout instead of std::cout ? I would like to use someting similar if possible.

Solution

The parameter you are looking for is search_path - that lists the schemas a query will look in. So, you can do something like:

CREATE TABLE schema1.tt ...
CREATE TABLE schema2.tt ...
CREATE FUNCTION schema1.foo() ...
CREATE FUNCTION schema2.foo() ...
SET search_path = schema1, something_else;
SELECT * FROM tt;        -- schema1.tt
SELECT * FROM schema2.tt -- schema2.tt
SELECT foo();            -- calls schema1.foo
SELECT schema2.foo();    -- calls schema2.foo

Note that if a query's plan gets saved inside the body of foo() then you may get an unexpected results. I would recommend you always explicitly list schemas for referenced tables in plpgsql functions if you are using duplicated tables. If not, make sure you have testing in place to check behaviour with a chaning search_path.

Oh - you can explicitly set search_path for a function's body too - see the manual's CREATE FUNCTION reference for details.

Comments

[Grzegorz Grzybek]

In SQL scripts you can use set schema 'schemaname'; and all unqualified table/view names will reference schemaname schema. See postgresql.org/docs/9.1/static/sql-set.html.

[Grzegorz Grzybek]

set session schema 'name', according to docs "Specifies that the command takes effect for the current session. (This is the default if neither SESSION nor LOCAL appears.)". I can't tell you now if it works for psycopg - probably yes.