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(); } }
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
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/
Cool! I must read your post in depth. Sounds Great