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 |