PostgreSQL function call model for PHP

This article describes a simple class that can be used to call PostgreSQL functions. Using the class has a number of advantages:

1. PostgreSQL functions can be called like they are PHP method functions with no special syntax.
2. The statements are automatically prepared giving faster execution for multiple calls.
3. No string escaping is needed for prepared statements.
4. The prepared statements are automatically deallocated when the object is destructed.

NOTE: PostgreSQL prepared statement support requries PHP 5.1 or later.

First, we create connect to PostgreSQL and create simple test function. The test function simply takes a string and a integer and concatenates a copy of the string the number of times specified by the integer.

$conn = pg_connect("dbname=test user=user1 password=user1");
if(!$conn) {
 echo "Unable to connect to test database\n";
 exit;
}

// Here is our test function. Notice the pretty pgEdit SQL syntax coloring in PHP :).
$test_func = <<<sql

create or replace function repeat_string (str text, how_many integer)
returns text as $$
declare
 result text := '';
begin
 if how_many is not null and how_many > 0 then
 for i in 1 .. how_many loop
 result := result || str;
 end loop;
 end if;
 return result;

end;
$$ language plpgsql;
sql;

// Create the function.
pg_query($conn, $test_func);

Next, let’s test the function, using the typical PHP model with strings. If this was a real application, a lot more parameter checking would be in order.

$res = pg_query($conn, "select repeat_string('ab', 3)");

echo "\n\nTest 1:\n";
echo pg_fetch_result($res, 0);

Test 1:
ababab

Now let’s create a class for calling PostgreSQL functions. We can create a single global instance of this class and use it throughout our PHP session. We could also create different instances if we are working with multiple connections. The key implementation detail takes advantage of the PHP __call magic method. This method is called whenever an unknown method for the instance is called. We can use this opportunity to call a PostgreSQL function with the same name and parameters.

class PgCall
{
 private $prepared = array();
 private $conn;

 // The contructor takes an existing connection, or a string to create a connection
 function __construct($connection_or_string) {
 if (is_string($connection_or_string)) $this->conn = pg_connect($c);
 else $this->conn = $connection_or_string;
 }

 // Kill all the prepared statements.
 function __destruct() {
 foreach($this->prepared as $statement) {
 $res = pg_query($this->conn, 'deallocate '  . $statement);
 }
 }

 // The __call magic method is called whenever an unknown method for the instance is called.
 function __call($fname, $fargs) {
 $statement = $fname . '__' . count($fargs);
 if (!in_array($statement, $this->prepared)) { // first time, not prepared yet
 $alist = array();
 for($i = 1; $i <= count($fargs); $i++) {
 $alist[$i] = '$' . $i;
 }
 $sql = 'select * from ' . $fname . '(' . implode(',', $alist) . ')';
 $prep = pg_prepare($this->conn, $statement, $sql);
 $this->prepared[] = $statement;
 }

 if ($res = pg_execute($this->conn, $statement, $fargs)) {
 $rows = pg_num_rows($res);
 $cols = pg_num_fields($res);
 if ($cols > 1) return $res; // return the cursor if more than 1 col
 else if ($rows == 0) return null;
 else if ($rows == 1) return pg_fetch_result($res, 0); // single result
 else return pg_fetch_all_columns($res, 0); // get column as an array
 }
 }
}

To use the class, we just create one instance of it and then we can call PostgreSQL functions without any special syntax or string escaping.

// Create an instance of the class using our existing connection.
$pg = new PgCall($conn);

echo "\n\nTest 2:\n";
echo $pg->repeat_string('a_b', 2);

Test 2:
a_ba_b

The class returns the full resource if the result has more than one column. It returns an array if there is more than one row and a single column. You could imagine a variety of strategies and perhaps design slightly different classes depending on your needs. The foremost advantage is to remove as much SQL as possible from PHP and take advantage of the security and performance benefits of prepared statements.

http://www.pgedit.com/resource/php/pgfuncall