How Do You Get a Web Page to Invoke a Postgres Command (e.g., for a RESTful API)?

Problem scenario
You want a REST call to invoke some SQL code on a Postgres database.  How do you do this?

Install Apache web server, PHP, and the PHP-pgsql package on your Postgresql database server.  This example assumes you have PHP, Apache web server, Postgres, and the php-pgsql package installed on your server.  Assuming the web server is running and the default directory for web pages is still /var/www/html/ do the following.

Create a .php file like this in your /var/www/html/ directory:




   $host        = "host=";
   $port        = "port=5432";
   $dbname      = "dbname=goodone";
   $credentials = "user=jdoe password=contintpass";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   pg_query("DROP DATABASE foobar");


You will have to change the dbname, user, and password in the PHP code above.  The user must be a role that has the LOGIN privilege with the Postgresql instance.  

Assuming you call the file above contint.php, browse to the web page (where x.x.x.x is the external IP address of the Postgres server):  http://x.x.x.x/contint.php 

Browsing to the above web page (contint.php with the code above) would invoke the SQL code in the pg_query parentheses.  In the example above, it would drop the database named "foobar."

This article above is for web-based invocation (e.g., RESTful commands).  To have PHP code invoke SQL commands in a Postgresql database without a web server, read this link.

Leave a comment

Your email address will not be published. Required fields are marked *