PostgreSQL functions

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/SQL3 language support, transaction integrity, and type extensibility. PostgreSQL is a public- domain, open source descendant of this original Berkeley code.

PostgreSQL is available without cost. The current version 6.3.2 is available at www.postgreSQL.org.

Since version 6.3 (03/02/1998) PostgreSQL use unix domain sockets, a table is given to this new 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 socket".

Table 1. Postmaster and PHP

One can also establish a connection with the following command: $conn = pg_Connect("host=localhost port=5432 dbname=chris");

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

Example 1. Using Large Objects

<?php

$database = pg_Connect ("", "", "", "", "jacarta"); pg_exec ($database, "begin");

$oid = pg_locreate ($database); echo ("$oid\n");

$handle = pg_loopen ($database, $oid, "w"); echo ("$handle\n");

pg_lowrite ($handle, "gaga"); pg_loclose ($handle);

pg_exec ($database, "commit") pg_exec ($database, "end")

?>

PostgreSQL functions

pg_Close

Name

pg_Close — closes a PostgreSQL connection

Description

void pg_close(int connection);

Returns false if connection is not a valid connection index, true otherwise. Closes down the connection to a PostgreSQL database associated with the given connection index.

pg_cmdTuples

Name

pg_cmdTuples — returns number of affected tuples

Description

int pg_cmdtuples(int result_id);

pg_cmdTuples() returns the number of tuples (instances) affected by INSERT, UPDATE, and DELETE queries. If no tuple is affected the function will return 0.

Example 1. pg_cmdtuples

<?php

$result = pg_exec($conn, "INSERT INTO verlag VALUES ('Autor')");

$cmdtuples = pg_cmdtuples($result);

echo $cmdtuples . " <- cmdtuples affected.";

?>

pg_Connect

Name

pg_Connect — opens a connection

Description

int pg_connect(string host, string port, string options, string tty, string

dbname);

Returns a connection index on success, or false if the connection could not be made. Opens a connection to a PostgreSQL database. Each of the arguments should be a quoted string, including the port number. The options and tty arguments are optional and can be left out. This function returns a connection index that is needed by other PostgreSQL functions. You can have multiple connections open at once.

A connection can also established with the following command: $conn = pg_connect("dbname=marliese port=5432"); Other parameters besides dbname and port are host, tty and options.

See also pg_pConnect.

pg_DBname

Name

pg_DBname — database name

Description

string pg_dbname(int connection);

Returns the name of the database that the given PostgreSQL connection index is connected to, or false if connection is not a valid connection index.

pg_ErrorMessage

Name

pg_ErrorMessage — error message

Description

string pg_errormessage(int connection);

Returns a string containing the error message, false on failure. Details about the error probably cannot be retrieved using the pg_errormessage() function if an error occured on the last database action for which a valid connection exists, this function will return a string containing the error message generated by the backend server.

pg_Exec

Name

pg_Exec — execute a query

Description

int pg_exec(int connection, string query);

Returns a result index if query could be executed, false on failure or if connection is not a valid connection index. Details about the error can be retrieved using the pg_ErrorMessage function if connection is valid. Sends an SQL statement to the PostgreSQL database specified by the connection index. The connection must be a valid index that was returned by pg_Connect. The return value of this function is an index to be used to access the results from other PostgreSQL functions.

PHP2 returned 1 if the query was not expected to return data (inserts or updates, for example) and greater than 1 even on selects that did not return anything. No such assumption can be made in PHP3.

pg_Fetch_Array

Name

pg_Fetch_Array — fetch row as array

Description

array pg_fetch_array(int result, int row);

Returns: An array that corresponds to the fetched row, or false if there are no more rows.

pg_fetch_array is an extended version of pg_fetch_row. In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

An important thing to note is that using pg_fetch_array is NOT significantly slower than using

pg_fetch_row, while it provides a significant added value. For further details, also see pg_fetch_row

Example 1. PostgreSQL fetch array

<?php

$conn = pg_pconnect("","","","","publisher"); if (!$conn) {

echo "An error occured.\n"; exit;

}

$result = pg_Exec ($conn, "SELECT * FROM authors"); if (!$result) {

echo "An error occured.\n"; exit;

}

$arr = pg_fetch_array ($result, 0); echo $arr[0] . " <- array\n";

$arr = pg_fetch_array ($result, 1); echo $arr["author"] . " <- array\n";

?>

pg_Fetch_Object

Name

pg_Fetch_Object — fetch row as object

Description

object pg_fetch_object(int result, int row);

Returns: An object with properties that correspond to the fetched row, or false if there are no more rows.

pg_fetch_object is similar to pg_fetch_array, with one difference

• an object is returned, instead of an array. Indirectly, that means that you can only access the data by the field names, and not by their offsets (numbers are illegal property names).

Speed-wise, the function is identical to pg_fetch_array, and almost as quick as pg_fetch_row (the difference is insignificant).

See also: pg_fetch_array and pg_fetch_row.

Example 1. Postgres fetch object

<?php

$database = "verlag";

$db_conn = pg_connect ("localhost", "5432", "", "", $database); if (!$db_conn): ?>

<H1>Failed connecting to postgres database <? echo $database ?></H1> <? exit;

endif;

$qu = pg_exec ($db_conn, "SELECT * FROM verlag ORDER BY autor");

$row = 0; // postgres needs a row counter other dbs might not

while ($data = pg_fetch_object ($qu, $row)): echo $data->autor." (";

echo $data->jahr ."): "; echo $data->titel."<BR>";

$row++; endwhile; ?>

<PRE><?

$fields[] = Array ("autor", "Author");

$fields[] = Array ("jahr", " Year");

$fields[] = Array ("titel", " Title");

$row= 0; // postgres needs a row counter other dbs might not while ($data = pg_fetch_object ($qu, $row)):

echo " \n";

reset ($fields);

while (list (,$item) = each ($fields)):

echo $item[1].": ".$data->$item[0]."\n"; endwhile;

$row++; endwhile;

echo "----------\n"; ?>

</PRE>

pg_Fetch_Row

Name

pg_Fetch_Row — get row as enumerated array

Description

array pg_fetch_row(int result, int row);

Returns: An array that corresponds to the fetched row, or false if there are no more rows.

pg_fetch_row fetches one row of data from the result associated with the specified result identifier. The row is returned as an array. Each result column is stored in an array offset, starting at offset 0.

Subsequent call to pg_fetch_row would return the next row in the result set, or false if there are no more rows.

See also: pg_fetch_array, pg_fetch_object, pg_result.

Example 1. Postgres fetch row

<?php

$conn = pg_pconnect("","","","","publisher"); if (!$conn) {

echo "An error occured.\n"; exit;

}

$result = pg_Exec ($conn, "SELECT * FROM authors"); if (!$result) {

echo "An error occured.\n"; exit;

}

$row = pg_fetch_row ($result, 0); echo $row[0] . " <- row\n";

$row = pg_fetch_row ($result, 1); echo $row[0] . " <- row\n";

$row = pg_fetch_row ($result, 2); echo $row[1] . " <- row\n";

?>

pg_FieldIsNull

Name

pg_FieldIsNull — Test if a field is NULL

Description

int pg_fieldisnull(int result_id, int row, mixed field);

Test if a field is NULL or not. Returns 0 if the field in the given row is not NULL. Returns 1 if the field in the given row is NULL. Field can be specified as number or fieldname. Row numbering starts at 0.

pg_FieldName

Name

pg_FieldName — Returns the name of a field

Description

string pg_fieldname(int result_id, int field_number);

pg_FieldName() will return the name of the field occupying the given column number in the given PostgreSQL result identifier. Field numbering starts from 0.

pg_FieldNum

Name

pg_FieldNum — Returns the number of a column

Description

int pg_fieldnum(int result_id, string field_name);

pg_FieldNum() will return the number of the column slot that corresponds to the named field in the given PosgreSQL result identifier. Field numbering starts at 0. This function will return -1 on error.

pg_FieldPrtLen

Name

pg_FieldPrtLen — Returns the printed length

Description

int pg_fieldprtlen(int result_id, int row_number, string field_name);

pg_FieldPrtLen() will return the actual printed length (number of characters) of a specific value in a PostgreSQL result. Row numbering starts at 0. This function will return -1 on an error.

pg_FieldSize

Name

pg_FieldSize — Returns the internal storage size of the named field

Description

int pg_fieldsize(int result_id, string field_name);

pg_FieldSize() will return the internal storage size (in bytes) of the named field in the given PostgreSQL result. A field size of -1 indicates a variable length field. This function will return false on error.

pg_FieldType

Name

pg_FieldType — Returns the type name for the corresponding field number

Description

int pg_fieldtype(int result_id, int field_number);

pg_FieldType() will return a string containing the type name of the given field in the given PostgreSQL result identifier. Field numbering starts at 0.

pg_FreeResult

Name

pg_FreeResult — Frees up memory

Description

int pg_freeresult(int result_id);

pg_FreeResult only needs to be called if you are worried about using too much memory while your script is running. All result memory will automatically be freed when the script is finished. But, if you are sure you are not going to need the result data anymore in a script, you may call pg_FreeResult with the result identifier as an argument and the associated result memory will be freed.

pg_GetLastOid

Name

pg_GetLastOid — Returns the last object identifier

Description

int pg_getlastoid(int result_id);

pg_GetLastOid can be used to retrieve the Oid assigned to an inserted tuple if the result identifier is used from the last command sent via pg_Exec and was an SQL INSERT. This function will return a positive integer if there was a valid Oid. It will return -1 if an error occured or the last command sent via pg_Exec was not an INSERT.

pg_Host

Name

pg_Host — Returns the host name

Description

string pg_host(int connection_id);

pg_Host() will return the host name of the given PostgreSQL connection identifier is connected to.

pg_loclose

Name

pg_loclose — close a large object

Description

void pg_loclose(int fd);

pg_loclose closes an Inversion Large Object. fd is a file descriptor for the large object from

pg_loopen.

pg_locreate

Name

pg_locreate — create a large object

Description

int pg_locreate(int conn);

pg_locreate creates an Inversion Large Object and returns the oid of the large object. conn specifies a valid database connection. PostgreSQL access modes INV_READ, INV_WRITE, and INV_ARCHIVE are not supported, the object is created always with both read and write access.

INV_ARCHIVE has been removed from PostgreSQL itself (version 6.3 and above).

pg_loopen

Name

pg_loopen — open a large object

Description

int pg_loopen(int conn, int objoid, string mode);

pg_loopen open an Inversion Large Object and returns file descriptor of the large object. The file descriptor encapsulates information about the connection. Do not close the connection before closing the large object file descriptor. objoid specifies a valid large object oid and mode can be either "r", "w", or "rw".

pg_loread

Name

pg_loread — read a large object

Description

string pg_loread(int fd, int len);

pg_loread reads at most len bytes from a large object and returns it as a string. fd specifies a valid large object file descriptor andlen specifies the maximum allowable size of the large object segment.

pg_loreadall

Name

pg_loreadall — read a entire large object

Description

void pg_loreadall(int fd);

pg_loreadall reads a large object and passes it straight through to the browser after sending all pending headers. Mainly intended for sending binary data like images or sound.

pg_lounlink

Name

pg_lounlink — delete a large object

Description

void pg_lounlink(int conn, int lobjid);

pg_lounlink deletes a large object with the lobjid identifier for that large object.

pg_lowrite

Name

pg_lowrite — write a large object

Description

int pg_lowrite(int fd, string buf);

pg_lowrite writes at most to a large object from a variable buf and returns the number of bytes actually written, or false in the case of an error. fd is a file descriptor for the large object from pg_loopen.

pg_NumFields

Name

pg_NumFields — Returns the number of fields

Description

int pg_numfields(int result_id);

pg_NumFields() will return the number of fields (columns) in a PostgreSQL result. The argument is a valid result identifier returned by pg_Exec. This function will return -1 on error.

pg_NumRows

Name

pg_NumRows — Returns the number of rows

Description

int pg_numrows(int result_id);

pg_NumRows will return the number of rows in a PostgreSQL result. The argument is a valid result identifier returned by pg_Exec. This function will return -1 on error.

pg_Options

Name

pg_Options — Returns options

Description

string pg_options(int connection_id);

pg_Options() will return a string containing the options specified on the given PostgreSQL connection identifier.

pg_pConnect

Name

pg_pConnect — make a persistent database connection

Description

int pg_pconnect(string host, string port, string options, string tty, string

dbname);

Returns a connection index on success, or false if the connection could not be made. Opens a persistent connection to a PostgreSQL database. Each of the arguments should be a quoted string, including the port number. The options and tty arguments are optional and can be left out. This function returns a connection index that is needed by other PostgreSQL functions. You can have multiple persistent connections open at once. See also pg_Connect.

A connection can also established with the following command: $conn = pg_pconnect("dbname=marliese port=5432"); Other parameters besides dbname and port are host, tty and options.

pg_Port

Name

pg_Port — Returns the port number

Description

int pg_port(int connection_id);

pg_Port() will return the port number that the given PostgreSQL connection identifier is connected to.

pg_Result

Name

pg_Result — Returns values from a result identifier

Description

mixed pg_result(int result_id, int row_number, mixed fieldname);

pg_Result() will return values from a result identifier produced by pg_Exec. The row_number and fieldname sepcify what cell in the table of results to return. Row numbering starts from 0. Instead of naming the field, you may use the field index as an unquoted number. Field indices start from 0.

PostgreSQL has many built in types and only the basic ones are directly supported here. All forms of integer, boolean and oid types are returned as integer values. All forms of float, and real types are returned as double values. All other types, including arrays are returned as strings formatted in the same default PostgreSQL manner that you would see in the psql program.

pg_tty

Name

pg_tty — Returns the tty name

Description

string pg_tty(int connection_id);

pg_tty() will return the tty name that server side debugging output is sent to on the given PostgreSQL connection identifier.