> This page location: PostgreSQL PHP > Call PostgreSQL Stored Procedures in PHP
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL PHP: Calling Stored Procedures

**Info:** Calling stored procedures from PHP with PDO works the same against any standard PostgreSQL database, so the patterns here carry over wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, [Lakebase](https://www.databricks.com/product/lakebase) delivers performance, security, and native integration with the Lakehouse. If you're a developer or startup who needs to ship fast and scale without friction, [Neon](https://neon.com) is the Postgres platform built for you.

**Summary**: in this tutorial, you will learn how to call stored procedures in PostgreSQL in PHP using PDO.

## Calling a stored procedure that returns one value

Let's create a simple [stored procedure](https://neon.com/postgresql/php/call-stored-procedures) named `add()` that returns the product of two integers using plpgsql.

```sql
CREATE OR REPLACE FUNCTION add(
    a INTEGER,
    b INTEGER)
  RETURNS integer AS $$
BEGIN
return a + b;
END; $$
  LANGUAGE 'plpgsql';
```

To call a stored procedure that returns one value, you use these steps:

1. [Connect to the PostgreSQL database server](https://neon.com/postgresql/php/connect) by creating a new instance of the PDO class.
2. Prepare the statement that calls the stored procedure for execution using the `prepare()` method of the PDO object. The `prepare()` method returns a `PDOStatement` object.
3. Optionally pass values to the statement using the `bindValue()` method.
4. Execute the statement using the `execute()` method of the `PDOStatement` object. You can pass the values to the statement when calling the `execute()` method as well.
5. Get the value using the `fetchColumn()` method that returns a single column of the next row in the result set.

The following `add()` method demonstrates how to call the `add()` stored procedure in PostgreSQL database.

```php
/**
 * Call a simple stored procedure
 * @param int $a
 * @param int $b
 * @return int
 */
public function add($a, $b) {
    $stmt = $this->pdo->prepare('SELECT * FROM add(:a,:b)');
    $stmt->setFetchMode(\PDO::FETCH_ASSOC);
    $stmt->execute([
        ':a' => $a,
        ':b' => $b
    ]);
    return $stmt->fetchColumn(0);
}
```

To test the `add()` method, you use the following code in the `index.php` file:

```php
<?php

require 'vendor/autoload.php';

use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\StoreProc as StoreProc;

try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $storeProc = new StoreProc($pdo);

    $result = $storeProc->add(20, 30);
    echo $result;

} catch (\PDOException $e) {
    echo $e->getMessage();
}
```

## Calling a stored procedure that returns a result set

We will use the `accounts`, `plans`, and `account_plans` tables for the sake of demonstration. The following `get_accounts()` stored procedure returns a result set that contains complete data of accounts.

```plsql
CREATE OR REPLACE FUNCTION get_accounts()
  RETURNS TABLE(id integer,
                first_name character varying,
                last_name character varying,
                plan character varying,
                effective_date date) AS
$$
BEGIN
 RETURN QUERY

 SELECT a.id,a.first_name,a.last_name, p.plan, ap.effective_date
 FROM accounts a
 INNER JOIN account_plans ap on a.id = account_id
 INNER JOIN plans p on p.id = plan_id
 ORDER BY a.id, ap.effective_date;
END; $$

LANGUAGE plpgsql;
```

The steps of calling a stored procedure that returns a result set are the same as the steps of [querying data](https://neon.com/postgresql/php/query).

The following `getAccounts()` method demonstrates how to call the `get_accounts()` stored procedure in PHP.

```php
/**
 * Call a stored procedure that returns a result set
 * @return array
 */
function getAccounts() {
    $stmt = $this->pdo->query('SELECT * FROM get_accounts()');
    $accounts = [];
    while ($row = $stmt->fetch()) {
        $accounts[] = [
            'id' => $row['id'],
            'first_name' => $row['first_name'],
            'last_name' => $row['last_name'],
            'plan' => $row['plan'],
            'effective_date' => $row['effective_date']
        ];
    }
    return $accounts;
}
```

To test the `getAccounts()` method, you use the following code in the `account.php` file.

```php
<?php
require 'vendor/autoload.php';

use PostgreSQLTutorial\Connection as Connection;
use PostgreSQLTutorial\StoreProc as StoreProc;

try {
    // connect to the PostgreSQL database
    $pdo = Connection::get()->connect();
    //
    $storeProc = new StoreProc($pdo);

    $accounts = $storeProc->getAccounts();

} catch (\PDOException $e) {
    echo $e->getMessage();
}
?>
<!DOCTYPE html>
<html>
    <head>
        <title>PostgreSQL PHP: calling stored procedure demo</title>
        <link rel="stylesheet" href="https://cdn.rawgit.com/twbs/bootstrap/v4-dev/dist/css/bootstrap.css">
    </head>
    <body>
        <div class="container">
            <h1>Account List</h1>
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                        <th>Plan</th>
                        <th>Effective Date</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($accounts as $account) : ?>
                        <tr>
                            <td><?php echo htmlspecialchars($account['id']) ?></td>
                            <td><?php echo htmlspecialchars($account['first_name']); ?></td>
                            <td><?php echo htmlspecialchars($account['last_name']); ?></td>
                            <td><?php echo htmlspecialchars($account['plan']); ?></td>
                            <td><?php echo htmlspecialchars($account['effective_date']); ?></td>
                        </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        </div>
    </body>
</html>
```

![D:\ref\projects\postgresql\php\stored procedure](https://neon.com/postgresqltutorial/PostgreSQL-PHP-store-procedure.png)In this tutorial, we have shown you how to call stored procedures from PostgreSQL using PHP PDO.

---

## Related docs (PostgreSQL PHP)

- [Connect to PostgreSQL Database Using PDO](https://neon.com/postgresql/php/connect)
- [Create New Tables in PHP](https://neon.com/postgresql/php/create-tables)
- [Insert Data Into Tables in PHP](https://neon.com/postgresql/php/insert)
- [Update Data In a Table using PHP](https://neon.com/postgresql/php/update)
- [Handle Transaction in PHP](https://neon.com/postgresql/php/transaction)
- [Query Data From PostgresQL using PDO](https://neon.com/postgresql/php/query)
- [Manage with BLOB in PHP](https://neon.com/postgresql/php/blob)
- [Delete Data From a Table in PHP](https://neon.com/postgresql/php/delete)