Using schema

["Daniel Caune" <daniel(dot)caune(at)ubisoft(dot)com>]

Hi,

I tried to figure out how to select a particular schema before executing
statement on its objects without having to specify the schema in these
statements (cf. the instruction USE supported in some other RDBMS)

PostgreSQL allows modifying dynamically the schema search path:

  SET search_path TO my_schema, public;

http://www.postgresql.org/docs/current/static/ddl-schemas.html
http://www.postgresql.org/docs/current/static/sql-set.html

By doing so, the creation statements will be executed in the schema
my_schema.

  SET search_path TO public;
  CREATE TABLE foo(i int);
  SET search_path TO my_schema, public;
  CREATE TABLE foo(i int);

             List of relations
  Schema   | Name  |   Type   |    Owner
-----------+------------------+-------------
 public    | bar   | table    | dbo_ubilive
 my_schema | foo   | table    | dbo_ubilive


The cool thing with this, compared to the USE statement supported by
some other RDBMS, is that the user is not restricted to one given schema
without explicit schema declaration:
 
  SELECT * FROM foo;                          -- Uses schema my_schema
  SELECT * FROM bar;                          -- Uses schema public
  SELECT * FROM foo, bar WHERE foo.i = bar.i; -- Uses both schemas

That is damn flexible! :-)

--
Daniel