An idea for calling PostgreSQL’s stored procedures with PDO


As far as I know PDO doesn’t allow to call directly the  PostgreSQL’s strored procedures. That’s not a problem. We can create a SQL and call a stored procedures as simple sql.
Imagine we have a stored procedure in the schema called ‘schemaName’ with the name ‘method1’.

CREATE OR REPLACE FUNCTION schemaName.method1(param1 numeric, param2 numeric)
  RETURNS numeric AS
$BODY$
BEGIN
   RETURN param1 + param2;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

The way of call it is something like this:
$conn = new PDO($dsn, $user, $password);
$conn->beginTransaction();
$stmt = $this->prepare("SELECT * FROM schemaName.method1(?, ?)");
$stmt->execute(1, 2);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$out = $stmt->fetchAll();
$conn->commit();
An idea for doing the same in a more clean way is:
$conn = new MyPDO($dsn, $user, $password);
$conn->beginTransaction();
$out = $conn->setSchema('schemaName')->method1(1, 2);
$conn->commit();
That’s only an approach. I haven’t think a lot about it but that’s OK as a first approach.
And now the class I’ve created extending PDO to obtain the above interface.
The trick is in __call function. Using __call I have dynamic functions in my MyPDO class and I will suppose every functions will be stored procedures.
class MyPDO extends PDO
{
    private $_schema = null; 

    /**
     * Set Schema
     *
     * @return MyPDO
     */
    public function setSchema($_squemaName)
    {
        $this->_schema = $_squemaName;
        return $this;
    } 

    function __call($method, $arguments)
    {
        $_params = array();
        if (count($arguments)>0) {
            for ($i=0; $i<count($arguments); $i++) {
                $_params[] = '?';
            }
        } 

        $stmt = $this->prepare("SELECT * FROM {$this->_schema}.{$method}(" .
            implode(', ', $_params) .  ")");
        $stmt->execute($arguments);
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        return $stmt->fetchAll();
    }
}
Advertisements

About Gonzalo Ayuso

Web Architect. PHP, Python, Node, Angular, ionic, PostgreSQL, Linux, ... Always learning.

Posted on December 8, 2009, in php, Web Development. Bookmark the permalink. 4 Comments.

  1. Another way I would suggest would be to leave the existing PDO as-is, and create another class that has a dependency on PDO. This code may format awfully, but here’s my shot at an example:

    $conn = new PDO($dsn, $user, $password);
    $conn->beginTransaction();
    $functor = new PDOFunctor($conn, ‘schemaName’);
    $functor->method1(1, 2);
    // or $conn->getFunctor(‘schemaName’)->method1(1, 2);
    $conn->commit();

    The main benefit as I see it is that the __call override is done on a separate class, so there’s no chance of a PGSQL stored procedure name clashing with one of PDO’s member functions. Otherwise, that poor sap that has to work with a database that has a stored procedure called beginTransaction is just out of luck with MyPDO.

    But all in all, this is a great idea, and definitely in the spirit of a language like PHP, Python, or Ruby.

    • Yes. that’s the main problem of this solution. Now I am working with other solution. I have a scaffolding script that creates classes with the plsql functions. It’s something similar to a ORM. Sometimes I don’t remember the name of the plsql function and I don`t want open pgadmin to search the function. With this other solution my PHP IDE will help me with the auto-completion and use plsql functions in the same way than I use PHP functions. I now with the scaffolding script I need some background processes but maybe it will work

  2. I see this is an older post but I thought I would chime in since I was recently doing research on calling postgresql functions and your post helped a bit. I put together an example of calling a function which returns a set of refcursors (essentially multiple queries) and returning the multiple result sets in php.

    http://trentrichardson.com/2012/01/04/return-multiple-result-sets-with-php-and-postgresql-functions/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: