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.

Answer 1

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.