Creating a Stored Procedure
- The first step is to write a stored procedure, the syntax is similar to that of the conventional SQL statements.
- The below code snippet shows a stored procedure that extracts the book names and book id, whose sales has crossed a certain benchmark amount.
- This stored procedure is named get_book_sales(sale_amount), which takes sale_amount as a parameter where the benchmark sales amount has to be passed.
- The stored procedure contains a nested query that extracts book names and ids of the books that crossed the benchmark sales amount.
- This stored procedure can be run as and when required.
- Save the below snippet in your PostgreSQL database as a stored procedure.
CREATE OR REPLACE FUNCTION get_book_sales(sale_amount integer)
RETURNS TABLE(book_name VARCHAR, book_id INTEGER, store VARCHAR) AS
$
BEGIN
RETURN QUERY
SELECT books.id, books.book_name,
FROM books where books.book_id IN
(SELECT book_id FROM book_sales where book_sales.sales > sale_amount)
END
$
LANGUAGE plpgsql
Execute PostgreSQL Stored Procedure and Function in Python
A stored procedure is a sequence of structured procedural language queries stored in a relational database management system as a data dictionary, which can be shared and reused multiple times. All CRUD operations, querying operations can be performed by calling the stored procedure. The use of stored procedures reduces the repetition of code that is used time and again. In this article let us discuss how to execute PostgreSQL stored procedure and function in python.