Running PostgreSQL stored procedures and functions

by Alex
Running PostgreSQL stored procedures and functions

In this tutorial, let’s look at how to execute PostgreSQL stored procedures and functions from Python. These functions can be responsible for both fetching and managing data.

Preparing the database

Before running the following program, make sure you have the following:

  • The user name and password to connect to the database.
  • Name of the stored procedure or function to be executed.

For this example, the get_production_deployment function was created to return a list of records of employees involved in writing the code.

CREATE OR REPLACE FUNCTION filter_by_price(max_price integer)
RETURNS TABLE(id INT, model TEXT, price REAL) AS $$
BEGIN
RETURN QUERY
SELECT * FROM mobile where mobile.price <= max_price;
END;
$$ LANGUAGE plpgsql;

Now let’s see how to execute it.


import psycopg2
from psycopg2 import
def create_func(query):
try:
# Connect to an existing database
connection = psycopg2.connect(user="postgres",
# password that you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
cursor.execute(query)
connection.commit()
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("PostgreSQL connection closed")
postgresql_func = """
CREATE OR REPLACE FUNCTION filter_by_price(max_price integer)
RETURNS TABLE(id INT, model TEXT, price REAL) AS $$
BEGIN
RETURN QUERY
SELECT * FROM mobile where mobile.price <= max_price;
END;
$$ LANGUAGE plpgsql;
"""
create_func(postgresql_func)

This code will add the function to the database.

PostgreSQL function and stored procedure call details

Let’s use the psycopg2 module to call the PostgreSQL function in Python. The following steps are required next:

  • The connect() method returns a new connection object. This is used to communicate with the database.
  • Create a Cursor object by using connect. It will allow you to make queries to the database.
  • Execute a function or stored procedure using the cursor.callproc() method. At this point you will need to know its name and the IN and OUT parameters. The syntax of the method is as follows. cursor.callproc('filter_by_price',[IN and OUT parameters,]) The parameters must be separated by commas.
  • This method returns either database strings or the number of strings changed. It all depends on the purpose of the function itself.

Example of function and stored procedure execution

Let’s look at the demo. The procedure filter_by_price is already created. It takes the application ID as an IN parameter and returns the ID, model and price of the phone.


import psycopg2
from psycopg2 import Error
try:
# Connect to an existing database
connection = psycopg2.connect(user="postgres",
# password that you used when you installed PostgreSQL
password="1111",
host="127.0.0.1",
port="5432",
database="postgres_db")
cursor = connection.cursor()
# stored procedure
cursor.callproc('filter_by_price',[999,])
print("Entries with a price less than or equal to 999")
result = cursor.fetchall()
for row in result:
print("Id = ", row[0], )
print("Model = ", row[1] )
print("Price = ", row[2])
except (Exception, Error) as error:
print("PostgreSQL error", error)
finally:
if connection:
cursor.close()
connection.close()
print("Connection to PostgreSQL closed")

After the previous tutorial I had only 2 entries left, the output returned one. The second with a price of 1000:

Entries with a price less than or equal to 999
Id = 2
Model = Google Pixel 2
Price = 700.0
Connection with PostgreSQL is closed

After that, process the results with fetchone. You can also use fetchall, fetchmany() methods, depending on what the function should return. Also cursor.callproc() internally calls the execute() method to call the procedure. So nothing prevents you from using it explicitly:

cursor.execute("SELECT * FROM filter_by_price({price}); ".format(price=1000))

Related Posts

LEAVE A COMMENT