Class PostgreSqlPlatform

__construct() 

Constructor.

Creates a new PostgreSqlPlatform.

getIdentifierQuoteCharacter() : string

Gets the character used for identifier quoting.

getSqlCommentStartString() : string

Gets the string portion that starts an SQL comment.

getSqlCommentEndString() : string

Gets the string portion that ends an SQL comment.

getVarcharMaxLength() : integer

Gets the maximum length of a varchar field.

getWildcards() : array

Gets all SQL wildcard characters of the platform.

getRegexpExpression() : string

regexp

the regular expression operator

getAvgExpression(string $column) : string

Returns the average value of a column

generated sql including an AVG aggregate function

getCountExpression(string|integer $column) : string

Returns the number of rows (without a NULL value) of a column

If a '*' is used instead of a column the number of selected rows is returned.

generated sql including a COUNT aggregate function

getMaxExpression(string $column) : string

Returns the highest value of a column

generated sql including a MAX aggregate function

getMinExpression(string $column) : string

Returns the lowest value of a column

getSumExpression(string $column) : string

Returns the total sum of a column

getMd5Expression( $column) : string

Returns the md5 sum of a field.

Note: Not SQL92, but common functionality

md5() works with the default PostgreSQL 8 versions.

If you are using PostgreSQL 7.x or older you need to make sure that the digest procedure is installed. If you use RPMS (Redhat and Mandrake) install the postgresql-contrib package. You must then install the procedure by running this shell command: psql [dbname] < /usr/share/pgsql/contrib/pgcrypto.sql You should make sure you run this as the postgres user.

getLengthExpression( $column) : string

Returns the length of a text field.

getRoundExpression( $column,  $decimals) : string

Rounds a numeric field to the number of decimals specified.

getModExpression(string $expression1, string $expression2) : string

Returns the remainder of the division operation $expression1 / $expression2.

getTrimExpression(string $str) : string

trim returns the string $str with leading and proceeding space characters removed

getRtrimExpression(string $str) : string

rtrim returns the string $str with proceeding space characters removed

getLtrimExpression(string $str) : string

ltrim returns the string $str with leading space characters removed

getUpperExpression(string $str) : string

upper Returns the string $str with all characters changed to uppercase according to the current character set mapping.

getLowerExpression(string $str) : string

lower Returns the string $str with all characters changed to lowercase according to the current character set mapping.

getLocateExpression(string $str, string $substr) : integer

locate returns the position of the first occurrence of substring $substr in string $str

getNowExpression() : string

Returns the SQL string to return the current system date and time.

getSubstringExpression(string $value, integer $from, integer $len = null) : string

Returns part of a string.

Note: Not SQL92, but common functionality.

sql that extracts part of a string.

getConcatExpression() : string

Returns a series of strings concatinated

concat() accepts an arbitrary number of parameters. Each parameter must contain an expression

getNotExpression( $expression) : string

Returns the SQL for a logical not.

Example: $q = new Doctrine_Query(); $e = $q->expr; $q->select('*')->from('table') ->where($e->eq('id', $e->not('null'));

a logical expression

getInExpression(string $column,  $values) : string

Returns the SQL to check if a value is one in a set of given values.

in() accepts an arbitrary number of parameters. The first parameter must always specify the value that should be matched against. Successive must contain a logical expression or an array with logical expressions. These expressions will be matched against the first parameter.

logical expression

getIsNullExpression(string $expression) : string

Returns SQL that checks if a expression is null.

logical expression

getIsNotNullExpression(string $expression) : string

Returns SQL that checks if a expression is not null.

logical expression

getBetweenExpression(string $expression, string $value1, string $value2) : string

Returns SQL that checks if an expression evaluates to a value between two values.

The parameter $expression is checked if it is between $value1 and $value2.

Note: There is a slight difference in the way BETWEEN works on some databases. http://www.w3schools.com/sql/sql_between.asp. If you want complete database independence you should avoid using between().

logical expression

getDropDatabaseSql(string $name) 

drop an existing database

\PDOException

getCreateTableSql(\unknown_type $name, array $fields, array $options = array()) : \unknown

Gets the SQL used to create a table.

getCreateSequenceSql(string $sequenceName, integer $start = 1, integer $allocationSize = 1) : string

Gets the SQL to create a sequence on this platform.

getCreateConstraintSql(string $table, string $name, array $definition) : string

Gets the SQL to create a constraint on a table on this platform.

getCreateIndexSql(string $table, string $name, array $definition) : string

Gets the SQL to create an index on a table on this platform.

quoteIdentifier(string $str) : string

Quotes a string so that it can be safely used as a table or column name, even if it is a reserved word of the platform.

NOTE: Just because you CAN use quoted identifiers doesn't mean you SHOULD use them. In general, they end up causing way more problems than they solve.

quoted identifier string

getCreateForeignKeySql(string $table, array $definition) : string

createForeignKeySql

getAlterTableSql(string $name, array $changes, boolean $check = false) : array

generates the sql for altering an existing table on postgresql

(this method is implemented by the drivers)

getColumnDeclarationListSql(array $fields) : string

Get declaration of a number of fields in bulk

getColumnDeclarationSql(string $name, array $field) : string

Obtain DBMS specific SQL code portion needed to declare a generic type field to be used in statements like CREATE TABLE.

DBMS specific SQL code portion that should be used to declare the column.

getDecimalTypeDeclarationSql(array $columnDef) : string

Gets the SQL snippet that declares a floating point column of arbitrary precision.

getBooleanTypeDeclarationSql(array $field) : string

Gets the SQL snippet that declares a boolean column.

getIntegerTypeDeclarationSql(array $field) : string

Gets the SQL snippet that declares a 4 byte integer column.

getBigIntTypeDeclarationSql(array $field) : string

Gets the SQL snippet that declares an 8 byte integer column.

getSmallIntTypeDeclarationSql(array $field) : string

Gets the SQL snippet that declares a 2 byte integer column.

getDefaultValueDeclarationSql(array $field) : string

Obtain DBMS specific SQL code portion needed to set a default value declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set a default value

getCheckDeclarationSql(array $definition) : string

Obtain DBMS specific SQL code portion needed to set a CHECK constraint declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set a CHECK constraint

getIndexDeclarationSql(string $name, array $definition) : string

Obtain DBMS specific SQL code portion needed to set an index declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set an index

getIndexFieldDeclarationListSql(array $fields) : string

getIndexFieldDeclarationList Obtain DBMS specific SQL code portion needed to set an index declaration to be used in statements like CREATE TABLE.

getTemporaryTableSql() : string

A method to return the required SQL string that fits between CREATE .

.. TABLE to create the table as a temporary table.

Should be overridden in driver classes to return the correct string for the specific database type.

The default is to return the string "TEMPORARY" - this will result in a SQL error for any database that does not support temporary tables, or that requires a different SQL command from "CREATE TEMPORARY TABLE".

The string required to be placed between "CREATE" and "TABLE" to generate a temporary table, if possible.

getShowDatabasesSql() : \unknown

Get sql query to show a list of database

getForeignKeyDeclarationSql(array $definition) : string

getForeignKeyDeclaration Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint of a field declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set the FOREIGN KEY constraint of a field declaration.

getAdvancedForeignKeyOptionsSql(array $definition) : string

getAdvancedForeignKeyOptions Return the FOREIGN KEY query section dealing with non-standard options as MATCH, INITIALLY DEFERRED, ON UPDATE, .

..

getForeignKeyReferentialActionSql(string $action) 

returns given referential action in uppercase if valid, otherwise throws an exception

\Doctrine_Exception_Exception

if unknown referential action given

getForeignKeyBaseDeclarationSql(array $definition) : string

Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint of a field declaration to be used in statements like CREATE TABLE.

getUniqueFieldDeclarationSql() : string

Obtain DBMS specific SQL code portion needed to set the UNIQUE constraint of a field declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set the UNIQUE constraint of a field declaration.

getColumnCharsetDeclarationSql(string $charset) : string

Obtain DBMS specific SQL code portion needed to set the CHARACTER SET of a field declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set the CHARACTER SET of a field declaration.

getColumnCollationDeclarationSql(string $collation) : string

Obtain DBMS specific SQL code portion needed to set the COLLATION of a field declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set the COLLATION of a field declaration.

getMatchPatternExpression(array $pattern, string $operator = null, string $field = null) : string

build a pattern matching string

EXPERIMENTAL

WARNING: this function is experimental and may change signature at any time until labelled as non-experimental

SQL pattern

prefersSequences() : boolean

Whether the platform prefers sequences for ID generation.

Subclasses should override this method to return TRUE if they prefer sequences.

prefersIdentityColumns() : boolean

Whether the platform prefers identity columns (eg. autoincrement) for ID generation.

Subclasses should override this method to return TRUE if they prefer identity columns.

writeLimitClause(string $query, mixed $limit = false, mixed $offset = false) 

Adds a LIMIT/OFFSET clause to the query.

This default implementation writes the syntax "LIMIT x OFFSET y" to the query which is supported by MySql, PostgreSql and Sqlite. Any database platforms that do not support this syntax should override this implementation and provide their own.

convertBooleans(array $item) 

Postgres wants boolean values converted to the strings 'true'/'false'.

Default conversion defined here converts to integers.

getSetCharsetSql(string $charset) : string

Gets the SQL statement specific for the platform to set the charset.

getCurrentDateSql() : string

Gets the SQL specific for the platform to get the current date.

getCurrentTimeSql() : string

Gets the SQL specific for the platform to get the current time.

getCurrentTimestampSql() : string

Gets the SQL specific for the platform to get the current timestamp

getDropSequenceSql(string $sequenceName) 

drop existing sequence

getCreateDatabaseSql(string $name) : void

create a new database

\PDOException

getSetTransactionIsolationSql(integer $level) 

Get sql to set the transaction isolation level

getCharsetFieldDeclaration(string $charset) : string

Obtain DBMS specific SQL code portion needed to set the CHARACTER SET of a field declaration to be used in statements like CREATE TABLE.

DBMS specific SQL code portion needed to set the CHARACTER SET of a field declaration.

getDateTimeTypeDeclarationSql(array $fieldDeclaration) : string

Obtain DBMS specific SQL to be used to create datetime fields in statements like CREATE TABLE

getDateTypeDeclarationSql(array $fieldDeclaration) : string

Obtain DBMS specific SQL to be used to create date fields in statements like CREATE TABLE.

getDefaultTransactionIsolationLevel() : integer

Gets the default transaction isolation level of the platform.

The default isolation level.

supportsSequences() : boolean

Whether the platform supports sequences.

Postgres has native support for sequences.

supportsIdentityColumns() : boolean

Whether the platform supports identity columns.

Postgres supports these through the SERIAL keyword.

supportsIndexes() : boolean

Whether the platform supports indexes.

supportsTransactions() : boolean

Whether the platform supports transactions.

supportsSavepoints() : boolean

Whether the platform supports savepoints.

supportsPrimaryConstraints() : boolean

Whether the platform supports primary key constraints.

supportsForeignKeyConstraints() : boolean

Whether the platform supports foreign key constraints.

supportsSchemas() : boolean

Whether the platform supports database schemas.

supportsGettingAffectedRows() : boolean

Whether the platform supports getting the affected rows of a recent update/delete type query.

getDateTimeFormatString() : string

Gets the format string, as accepted by the date() function, that describes the format of a stored datetime value of this platform.

The format string.

getDateFormatString() : string

Gets the format string, as accepted by the date() function, that describes the format of a stored date value of this platform.

The format string.

getTimeFormatString() : string

Gets the format string, as accepted by the date() function, that describes the format of a stored time value of this platform.

The format string.

getVarcharTypeDeclarationSql(array $field) 

Gets the SQL snippet used to declare a VARCHAR column on the MySql platform.

getClobTypeDeclarationSql(array $field) 

Gets the SQL snippet used to declare a CLOB column type.

getName() : string

Get the platform name for this instance

getSqlResultCasing(string $column) : string

Gets the character casing of a column in an SQL result set.

PostgreSQL returns all column names in SQL result sets in lowercase.

The column name in the character casing used in SQL result sets.

fixSchemaElementName( $schemaElementName) : string

Makes any fixes to a name of a schema element (table, sequence, .

..) that are required by restrictions of the platform, like a maximum length.

getEmptyIdentityInsertSql( $quotedTableName,  $quotedIdentifierColumnName) : string

Get the insert sql for an empty insert statement

$sql

getAgeExpression(string $timestamp1, string $timestamp2 = null) : string

PostgreSQLs AGE(<timestamp1> [, <timestamp2>]) function.

getDatePartExpression(string $text, string $time) : string

PostgreSQLs DATE_PART( <text>, <time> ) function.

getToCharExpression(string $time, string $text) : string

PostgreSQLs TO_CHAR( <time>, <text> ) function.

getRandomExpression() : \return

return string to call a function to get random value inside an SQL statement

string to generate float between 0 and 1

_getCommonIntegerTypeDeclarationSql(array $columnDef) : string

Gets the SQL snippet that declares common properties of an integer column.

_getTransactionIsolationLevelSql(integer $level) 

Get sql for transaction isolation level Connection constant