Server/Database/Schema Definitions

On Tue, May 16, 2006 at 09:23:09AM -0500, 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?

You can do that, yes. You can also change search_path; any object not
created in a specific schema will go into the first schema listed in
search_path.

You can set a default tablespace in postgresql.conf, at the database
level, and I think at the session level as well.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

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