Re: Server/Database/Schema Definitions

On May 16, 2006, at 10:23 AM, Brandon E Hofmann wrote:

> When coding table and function scripts, how do you specify which
> server/database/schema where you want new tables and functions to
> reside?
> Every time I execute my creation scripts, it puts everything in the
> public
> schema under the PostgreSQL server. I'm new to PostgreSQL and haven't
> found any documentation discussing these characteristics.
>
> Do you explicitly define the server.schema.table_name in the create
> table
> statement? Should the tablespace be set to pg_default and the
> owner to
> postgres?

See this page for a discussion of search_path

http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html

I typically set the search path at the top of the script. But doing
it like this can certainly trip you up in cases where you might make
changes and do not execute the script from top to bottom (e.g.
redefine a single function).

If I have a specific user (or role) for a certain database or
project, I like to associate the search path with the role so I don't
have to remember to do it every time I work on the database. See
ALTER ROLE SET syntax for details on this:

http://www.postgresql.org/docs/8.1/interactive/sql-alterrole.html

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL