LXXVIII. PostgreSQL functions

OLD NAME           NEW NAME
pg_exec            pg_query
getlastoid         pg_last_oid
pg_cmdtuples       pg_affected_rows
pg_errormessage    pg_last_error
pg_numrows         pg_num_rows
pg_numfields       pg_num_fields
pg_fieldname       pg_field_name
pg_fieldsize       pg_field_size
pg_fieldtype       pg_field_type
pg_fieldnum        pg_field_num
pg_fieldprtlen     pg_field_prtlen
pg_fieldisnull     pg_field_is_null
pg_freeresult      pg_free_result
pg_result          pg_fetch_result
pg_loreadall       pg_lo_read_all
pg_locreate        pg_lo_create
pg_lounlink        pg_lo_unlink
pg_loopen          pg_lo_open
pg_loclose         pg_lo_close
pg_loread          pg_lo_read
pg_lowrite         pg_lo_write
pg_loimport        pg_lo_import
pg_loexport        pg_lo_export

Postgres, developed originally in the UC Berkeley Computer Science Department, pioneered many of the object-relational concepts now becoming available in some commercial databases. It provides SQL92/SQL99 language support, transaction integrity and type extensibility. PostgreSQL is an open source descendant of this original Berkeley code.

PostgreSQL database is Open Source product and available without cost. To use PostgreSQL support, you need PostgreSQL 6.5 or later. PostgreSQL 7.0 or later to enable all PostgreSQL module feature. PostgreSQL supports many character encoding including multibyte character encoding. The current version and more information about PostgreSQL is available at www.postgresql.org.

In order to enable PostgreSQL support, "--with-pgsql[=DIR]" is required when you compile PHP. If shared object module is available, PostgreSQL module may be loaded using extension directive in php.ini or dl() function. Supported ini directives are described in php.ini-dist file which comes with source distribution.

Not all functions are supported by all builds. It depends on your libpq (The PostgreSQL C Client interface) version and how libpq is compiled. If there is missing function, libpq does not support the feature required for the function.

It is also important that you use newer libpq than PostgreSQL Server to be connected. If you use libpq older than PostgreSQL Server expects, you may have problems.

Since version 6.3 (03/02/1998) PostgreSQL uses unix domain sockets by default. TCP port will NOT be opened by default. A table is shown below describing these new connection possibilities. This socket will be found in /tmp/.s.PGSQL.5432. This option can be enabled with the '-i' flag to postmaster and it's meaning is: "listen on TCP/IP sockets as well as Unix domain sockets".

A connection to PostgreSQL server can be established with the following value pairs set in the command string: $conn = pg_connect("host=myHost port=myPort tty=myTTY options=myOptions dbname=myDB user=myUser password=myPassword ");

The previous syntax of: $conn = pg_connect ("host", "port", "options", "tty", "dbname") has been deprecated.

Environmental variable affects PostgreSQL server/client behavior. For example, PostgreSQL module will lookup PGHOST environment variable when hostname is omitted in connection string. Supported environment variables are different from version to version. Refer to PostgreSQL Programmer's Manual (libpq - Environment Variables) for details.

From PostgreSQL 7.1.0, text data type has 1GB as its max size. Older PostgreSQL's text data type is limited by block size. (Default 8KB. Max 32KB defined at compile time)

To use the large object (lo) interface, it is required to enclose large object functions within a transaction block. A transaction block starts with a SQL statement begin and if the transaction was valid ends with commit or end. If the transaction fails the transaction should be closed with rollback or abort.

Example 2. Using Large Objects <?php $database = pg_connect ("dbname=jacarta"); pg_exec ($database, "begin"); $oid = pg_locreate ($database); echo ("$oid\n"); $handle = pg_loopen ($database, $oid, "w"); echo ("$handle\n"); pg_lowrite ($handle, "large object data"); pg_loclose ($handle); pg_exec ($database, "commit"); ?>

Do not close connection resource before closing large object resource.