Overview
- Like any other database management system PostgreSQL as well allows users to define routines on the server, which are popularly known as Stored Procedures. Prior to version 11, only functions can be registered with PostgreSQL. Ability to add procedures as well has been introduced in PostgreSQL version 11.
- The difference between a procedure and a function is that a procedure cannot be used in a mathematical expression, as it does not return any value. On to the contrary a function can be used in any mathematical expression as it returns a value.
- Such procedures and functions are often written using multiple SQL statements and perform a sequence of data processing activities.
- Any SQL interface can be used to invoke a stored procedure or a function registered in the PostgreSQL server.
- The example below shows that how a function defined and stored in a PostgreSQL server is invoked from a Python Program using psycopg.
Benefits of using a stored procedure
- Frequently used code yet bigger in terms of number of lines of SQL statements can often be bundled as a stored procedure or function. They provide several benefits as listed below:
- Network bandwidth used is minimal as only the name of the stored procedure or the function is sent from the client to the database server.
- The underlying tables involved need to not be known to the clients. Hence no need for granting the clients any privileges for the table. It is just sufficient to provide only the execute permission for the stored procedures.
- The business logic is centrally put in one place and any number of clients who have the “Execute” permission can use the stored procedures.
- The internals of the stored procedure or business logic is hidden to the clients.
Calling a PostgreSQL stored procedure from a Python Program
- Import psycopg into the python program
- Create a connection object by specifying the IP address and the credentials required to connect to the PostgreSQL server.
- From the connection object create a session object which can be used for executing any SQL statement.
- The stored procedure, which needs to be called from the Python Program, can be sent using the session object.
- The results are iterated and printed onto the console.
A stored function/procedure registered with PostgreSQL
CREATE OR REPLACE FUNCTION ExpireOrders()
RETURNS boolean
AS $BODY$
DECLARE
singleOrder RECORD;
BEGIN
FOR singleOrder IN
SELECT * FROM orders
LOOP
if singleOrder.status = 'ORDD' then
update orders set status = 'EXEC' where id = singleOrder.id;
end if;
END LOOP;
IF NOT FOUND THEN
return false;
END IF;
return true;
END;
$BODY$ LANGUAGE plpgsql;- The above command to create the stored function ExpireOrders()can be registered by executing it on the PSQL console of PostgreSQL.
- Alternatively the command can be executed through a Python program using a cursor object.
Example
# import the Python client for PostgreSQL
import psycopg2
import psycopg2.extras
# Name of the table
tableName = "orders"
# Obtain a connection object to the PostgreSQL database server
postgresConnection = psycopg2.connect("dbname=test user=test password='test'")
# Obtain a dictionary cursor from the connection object
cursorObject = postgresConnection.cursor(cursor_factory=psycopg2.extras.DictCursor)
# Execute the stored function/procedure
print("Runnning the function ExpireOrders()...");
cursorObject.execute("select ExpireOrders()")
cursorObject.execute("select * from orders")
rows = cursorObject.fetchall()
print("Orders and their status after expiring them through stored function/procedure:")
for row in rows:
print("%d %s %s %s"%(row["exchange_id"],row["scrip_id"],row["direction"],row["status"]))Output
Status of orders before executing the stored function
test=> select * from orders;
id | exchange_id | created | scrip_id | price | quantity | status | direction
-------------+-------------+---------------------+----------+---------+----------+--------+-----------
21474836472 | 31374436441 | 2018-08-09 06:43:36 | MSFT | $108.08 | 10 | EXEC | S
21474836481 | 31374436453 | 2018-08-09 06:43:37 | MSFT | $108.11 | 10 | EXEC | S
21474836492 | 31374436461 | 2018-08-09 06:43:38 | MSFT | $108.13 | 10 | EXEC | S
21474836494 | 31374436481 | 2018-08-09 06:43:40 | MSFT | $108.03 | 10 | EXEC | S
21474836451 | 31374436432 | 2018-08-09 06:43:34 | MSFT | $110.04 | 10 | ORDD | S
21474836451 | 31374436432 | 2018-08-09 06:43:34 | MSFT | $110.04 | 10 | ORDD | S
21474836493 | 31374436471 | 2018-08-09 06:43:39 | MSFT | $111.14 | 10 | ORDD | S
(7 rows)Status of orders before executing the stored function
Running the function ExpireOrders()...
Orders and their status after expiring them through stored function/procedure:
31374436441 MSFT S EXEC
31374436453 MSFT S EXEC
31374436461 MSFT S EXEC
31374436481 MSFT S EXEC
31374436432 MSFT S EXEC
31374436432 MSFT S EXEC
31374436471 MSFT S EXEC