PostgreSQL

To create a PDM with support for features specific to the PostgreSQL DBMS family, select the appropriate version in the DBMS field of the New Model dialog. To view these extensions to the PowerDesigner metamodel in the Resource Editor, select Database > Edit Current DBMS and expand the Profile node.

Note: The DBMS definition file for PostgreSQL v7.3 is deprecated.

The following sections list the extensions provided for PostgreSQL.

Databases

The following extensions are available on the PostgreSQL tab:

Name

Description

Template

The name of the template from which to create the new database, or DEFAULT to use the default template.

Scripting name: Template

Encoding

Character set encoding to use in the new database. Specify a string constant (e.g., 'SQL_ASCII'), or an integer encoding number, or DEFAULT to use the default encoding.

Scripting name: Encoding

Domains

The following extensions are available on the PostgreSQL tab. To display this tab, select BaseType or CompositeType in the Stereotype field on the General tab and click Apply:

Name

Description

Definition

[Composite Type] The composite type is specified by a list of attribute names and data types. This is essentially the same as the row type of a table, but using CREATE TYPE avoids the need to create an actual table when all that is wanted is to define a type. A stand-alone composite type is useful as the argument or return type of a function.

Scripting name: CompositeDefinition

Length

[Base Type] Specifies the internal length of the new type.

Scripting name: ExtTypeLength

Array Element type

[Base Type] Specifies the type of the array elements.

Scripting name: ExtTypeElement

Array delimiter

[Base Type] Specifies the delimiter character for the array.

Scripting name: ExtTypeDelimiter

By Value

[Base Type] Specifies that operators and functions which use this data type should be passed an argument by value rather than by reference.

Scripting name: ExtTypePassedByValue

Input function

[Base Type] Specifies the name of a function, created by CREATE FUNCTION, which converts data from its external form to the internal form of the type.

Scripting name: ExtTypeInput

Output function

[Base Type] Specifies the name of a function, created by CREATE FUNCTION, which converts data from its internal form to a form suitable for display.

Scripting name: ExtTypeOutput

Send function

[Base Type] Specifies the name of a function, created by CREATE FUNCTION, which converts data of this type into a form suitable for transmission to another machine.

Scripting name: ExtTypeSend

Receive function

[Base Type] Specifies the name of a function, created by CREATE FUNCTION, which converts data of this type from a form suitable for transmission from another machine to internal form.

Scripting name: ExtTypeReceive

Groups

The following extensions are available on the PostgreSQL tab (v8 and higher):

Name

Description

Group identifier (id)

The SYSID clause can be used to choose the PostgreSQL group ID of the new group. This is normally not necessary, but may be useful if you need to recreate a group referenced in the permissions of some object.

Scripting name: SysId

Procedures

The following extensions are available on the PostgreSQL tab:

Name

Description

Language

The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. (See also extended attribute type ProcLanguageList.)

Scripting name: ProcLanguage

References

The following extensions are available on the PostgreSQL tab (v8 and higher):

Name

Description

Deferrable

Controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable may be postponed until the end of the transaction.

Only foreign key constraints currently accept this clause. All other constraint types are not deferrable.

Scripting name: Deferrable

Foreign key constraint deferred

If a constraint is deferrable, this clause specifies the default time to check the constraint.

False means the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default.

True means the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction.

Scripting name: ForeignKeyConstraintDeferred

Tables

The following extensions are available on the PostgreSQL tab (v8 and higher):

Name

Description

Temporary state

If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction.

Scripting name: Temporary

Tablespaces

The following extensions are available on the PostgreSQL tab (v8 and higher):

Name

Description

Location

Specifies the directory that will be used for the tablespace. The directory must be specified by an absolute path name.

Scripting name: TbspLocation

Owner

Specifies the name of the user who will own the tablespace. If omitted, defaults to the user executing the command. Only superusers may create tablespaces, but they can assign ownership of tablespaces to non-superusers.

Scripting name: TbspOwner

Users

The following extensions are available on the General tab (v8 and higher):

Name

Description

Is schema

Specifies that the user is a schema.

If TRUE, the user is allowed to create databases.

Scripting name: Schema

Owner

[schemas] Specifies the owner of the schema.

Scripting name: Owner

The following extensions are available on the PostgreSQL tab (v8 and higher):

Name

Description

User identifier (id)

Specifies the PostgreSQL user ID of the new user. This is normally not necessary, but may be useful if you need to recreate the owner of an orphaned object.

Scripting name: SysId

Create database

Specifies that the user can create databases.

Scripting name: CreateDB

Create user

Specifies that the user can create users and turns the user into a superuser who can override all access restrictions.

Scripting name: CreateUser

Validity

Specifies an absolute time after which the user's password is no longer valid. By default, the password will be valid forever.

Scripting name: Validity

Encrypted password

Specifies that the password is stored encrypted in the system catalogs.

Scripting name: EncryptedPassword