Pgfunc Laravel Package

Getting Started

Minimal Setup

1. Installation

   composer require red-defender/pgfunc
   

   Add the service provider to config/app.php under providers:

   RedDefender\PgFunc\PgFuncServiceProvider::class,
   

2. Configuration Publish the config file:

   php artisan vendor:publish --provider="RedDefender\PgFunc\PgFuncServiceProvider"
   

   Update config/pgfunc.php with your PostgreSQL connection details (e.g., host, port, dbname, user, password).

3. First Use Case: Basic Connection Inject the PgFunc facade or service into a controller/service:

   use RedDefender\PgFunc\Facades\PgFunc;
   
   public function testConnection()
   {
       $conn = PgFunc::connection(); // Returns a PDO instance
       $result = $conn->query("SELECT version()")->fetchColumn();
       return $result; // e.g., "PostgreSQL 15.3"
   }
   

Implementation Patterns

Core Workflows

1. Transactions

Use PgFunc to wrap transactions with rollback-on-failure:

PgFunc::transaction(function ($conn) {
    $conn->exec("INSERT INTO users (name) VALUES ('John')");
    $conn->exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1");
});

• Tip: Pass a closure to handle the transaction logic. The connection is auto-closed on success/failure.

2. Stored Procedures

Execute procedures with parameters:

$result = PgFunc::call('sp_update_user_balance', [
    'user_id' => 1,
    'amount' => -50,
]);

• Output Handling: Use fetchAll(), fetch(), or fetchColumn() on the returned PDOStatement.

3. Connection Management

• Named Connections: Define multiple connections in config/pgfunc.php and switch dynamically:

  $conn = PgFunc::connection('secondary_db');
  

• Lazy Connections: Connections are established on first use (no overhead until needed).

4. Query Building

Combine with Laravel’s query builder for hybrid workflows:

$users = PgFunc::connection()->query(
    "SELECT * FROM users WHERE created_at > NOW() - INTERVAL '1 year'"
)->fetchAll(PDO::FETCH_ASSOC);

Integration Tips

Laravel Eloquent

Use PgFunc for raw SQL when Eloquent is overkill:

$rawData = PgFunc::connection()->query("SELECT * FROM complex_view")->fetchAll();

Event-Driven Workflows

Pair with Laravel events for async operations:

event(new UserBalanceUpdated($userId, $amount));
PgFunc::transaction(function ($conn) use ($userId, $amount) {
    // Update balance via stored procedure
});

Testing

Mock PgFunc in tests using Laravel’s Mockery:

$mock = Mockery::mock('alias:PgFunc');
$mock->shouldReceive('call')->andReturn([...]);

Gotchas and Tips

Pitfalls

1. Connection Leaks

   • Issue: Forgetting to close connections manually (though PgFunc handles this in transactions).
   • Fix: Prefer PgFunc::transaction() over raw PgFunc::connection() for short-lived operations.

2. Stored Procedure Errors

   • Issue: Silent failures if procedures throw exceptions.
   • Fix: Wrap calls in try-catch:

     try {
         PgFunc::call('sp_risky_operation', [...]);
     } catch (\Exception $e) {
         Log::error("Procedure failed: " . $e->getMessage());
     }
     

3. Parameter Binding Quirks

   • Issue: PostgreSQL-specific types (e.g., jsonb) may require explicit casting.
   • Fix: Use PDO::PARAM_STR or cast in SQL:

     $result = PgFunc::call('sp_process_json', [
         'data' => json_encode($array), // Cast to JSON in the procedure
     ]);
     

4. Config Overrides

   • Issue: Changes to config/pgfunc.php require a restart or config:clear.
   • Fix: Use environment variables for dynamic configs:

     'connections' => [
         'primary' => [
             'host' => env('DB_HOST', 'localhost'),
         ],
     ],
     

Debugging Tips

1. Enable PDO Logging Add to config/pgfunc.php:

   'logging' => true,
   

   Logs will appear in Laravel’s log channel.

2. Query Inspection Use PgFunc::getLastQuery() (if supported) or wrap in a debug helper:

   $start = microtime(true);
   $result = PgFunc::call('sp_slow_proc', [...]);
   $duration = microtime(true) - $start;
   Log::debug("Query took {$duration}s");
   

3. Transaction Debugging For long-running transactions, add manual checkpoints:

   PgFunc::transaction(function ($conn) {
       $conn->exec("SAVEPOINT before_update");
       // ... operations ...
       $conn->exec("RELEASE SAVEPOINT before_update");
   });
   

Extension Points

1. Custom Connection Resolvers Extend the service provider to resolve connections from external sources (e.g., Redis):

   // In a custom service provider
   $this->app->bind('pgfunc.connection.resolver', function () {
       return new CustomConnectionResolver();
   });
   

2. Procedure Metadata Cache procedure signatures for autocompletion (e.g., in IDEs):

   $signature = PgFunc::describeProcedure('sp_update_user');
   // Returns: ["user_id" => "integer", "amount" => "numeric"]
   

3. Event Listeners Trigger events before/after procedure calls:

   PgFunc::listen('beforeCall', function ($procedure, $params) {
       Log::info("Calling {$procedure} with params: " . json_encode($params));
   });