schema-keeper/executor

Simple interaction with PostgreSQL stored procedures

v1.1.0 2019-06-27 13:42 UTC

Requires

• php: >=5.6
• ext-pdo: *
• ext-pdo_pgsql: *

Requires (Dev)

• mockery/mockery: ^1.0
• phpunit/phpunit: ^5.0||^6.0||^7.0||^8.0
• squizlabs/php_codesniffer: ^3.0

MIT

• Dmytro Demchyna

• database
• db
• invoke
• exception
• postgresql
• function
• pgsql
• postgres
• hint
• call
• exec
• plpgsql
• execute
• stored procedure
• raise

README

The Executor helps to execute PL/pgSQL stored procedures.

Examples

Exception handling

Suppose stored procedure throws an exception:

CREATE OR REPLACE FUNCTION public.test_function() 
  RETURNS VOID
  LANGUAGE plpgsql
AS $function$
BEGIN
   RAISE EXCEPTION 'MyException' USING HINT = 'TestHint';
END;
$function$;

The way to call it via Executor:

<?php

use SchemaKeeper\Tools\Executor\Fetcher\SingleColumn;
use SchemaKeeper\Tools\Executor\Exception\RaisedException;

try {
    $executor->execFunc('public.test_function', [], new SingleColumn()); 
} catch (RaisedException $e) {
    echo $e->getExceptionName().' '.$e->getExceptionHint();
}

Echo in the catch block will output: "MyException TestHint"

Fetching

Suppose stored procedure returns a table:

CREATE OR REPLACE FUNCTION public.test_function(_dummy TEXT)
    RETURNS TABLE(param1 INTEGER, param2 TEXT)
    LANGUAGE plpgsql
AS $function$
BEGIN
    param1 = 1;
    param2 = 'One';
    RETURN NEXT;

    param1 = 2;
    param2 = 'Two';
    RETURN NEXT;

    RETURN;
END;
$function$;

Executor provides three different fetchers:

<?php

use SchemaKeeper\Tools\Executor\Fetcher\SingleColumn;
use SchemaKeeper\Tools\Executor\Fetcher\SingleRow;
use SchemaKeeper\Tools\Executor\Fetcher\MultipleRow;

$params = [':dummy' => 'test'];

$result1 = $executor->execFunc('public.test_function', $params, new SingleColumn());
$result2 = $executor->execFunc('public.test_function', $params, new SingleRow());
$result3 = $executor->execFunc('public.test_function', $params, new MultipleRow());

var_dump($result1, $result2, $result3);

var_dump will output:

int(1)

array(2) {
  'param1' =>
  int(1)
  'param2' =>
  string(3) "One"
}

array(2) {
  [0] =>
  array(2) {
    'param1' =>
    int(1)
    'param2' =>
    string(3) "One"
  }
  [1] =>
  array(2) {
    'param1' =>
    int(2)
    'param2' =>
    string(3) "Two"
  }
}

Initialization

<?php

use SchemaKeeper\Tools\Executor\Connection\PDOProxy;
use SchemaKeeper\Tools\Executor\ErrorHandler;
use SchemaKeeper\Tools\Executor\Executor;

$dsn = 'pgsql:dbname=schema_keeper;host=localhost';

$pdo = new PDO($dsn, 'postgres', 'postgres', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

$conn = new PDOProxy($pdo);

$errorHandler = new ErrorHandler();

$executor = new Executor($conn, $errorHandler);

Contributing

Please refer to CONTRIBUTING.md for information on how to contribute to SchemaKeeper.