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