Enter the terms you wish to search for.

How to call PostgreSQL stored procedures from a PHP application

January 19, 2023

SUMMARY: This tutorial provides instructions and an example for calling a PostgreSQL stored procedure from a PHP application.

1. Creating the PostgreSQL stored procedure

2. Creating the example program in PHP

3. Running the example program

Prerequisites 

For this demonstration we will be using the following software versions:

  1. CentOS Linux release 7.5.1804
  2. PostgreSQL 12
  3. Apache 2.4
  4. PHP 7

 

Example of using a stored procedure in a PHP application

1. Creating the PostgreSQL stored procedure 

The example program will call the following stored procedure, which adds two complex numbers and returns the result in INOUT parameters. Let's start by creating a stored procedure:

CREATE OR REPLACE PROCEDURE add_complex(IN real_1 INTEGER,

                                        IN imaginary_1 INTEGER,

                                        IN real_2 INTEGER,

                                        IN imaginary_2 INTEGER,

                                        INOUT real_res INTEGER,

                                        INOUT imaginary_res INTEGER)

AS $$

BEGIN

  real_res := real_1 + real_2;

  imaginary_res := imaginary_1 + imaginary_2;

END;

$$

LANGUAGE plpgsql;

 

2. Creating the example program in PHP

Now, let’s call this stored procedure using PHP.

[abbas@localhost html]$ pwd

/var/www/html

 

Create the following file sp.php at the above location:

sp.php

<?php

  $dbconn = pg_connect("host=127.0.0.1 user=postgres dbname=postgres port=5432")

  or die('Could not connect: ' . pg_last_error());



  $real_1 = 1;

  $imaginary_1 = 2;

  $real_2 = 3;

  $imaginary_2 = 4;



  $result = pg_query_params($dbconn, 'CALL add_complex($1, $2, $3, $4, NULL, NULL)', 

                array($real_1, $imaginary_1, $real_2, $imaginary_2))

              or die('Unable to CALL stored procedure: ' . pg_last_error());



  $row = pg_fetch_row($result);

  $res_real = $row[0];

  $res_imaginary = $row[1];

  pg_free_result($result);

  echo "<p>($real_1+i$imaginary_1)+($real_2+i$imaginary_2)=($res_real+i$res_imaginary)</p>";



  pg_close($dbconn);

?>

[abbas@localhost html]$ pwd

/var/www/html

 

Update the file index.html at the above location with the content as shown below:

index.html 

<html>

<head>

<title>Stored Procedure from a PHP application</title>

</head>

<body>

<p><a href="sp.php">Execute SP to add two complex numbers</a></p>

</body>

</html>

 

3. Running the example program

Now open the file in browser 127.0.0.1 and click on “Execute SP to add two complex numbers”:

 

 

 

 

 

Share this

Relevant Blogs

More Blogs

An Overview of PostgreSQL Indexes

h2 { text-align: left !important; } .summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes...
January 24, 2023