Postgres Stored Procedures with Input and Output Parameters SQL
In this tutorial, we learn to create Postgres Stored Procedures with input and output parameters in SQL. We’ll include:
- What? What do PostgreSQL relational database’s Stored Procedure do and what is the syntax?
- Why? Why use Stored Procedure?
- How? How and why do we use a Stored Procedure, where can we use it in our SQL scripts, and how do we refer to them from Python code?
- Knowledge of the use of the most basic SQL commands, including
- Optional: Knowledge of Python.
What is a “Stored Procedure” and how does it work?
Some features of Stored Procedures:
- We can refer to SPs as a “named program” that is stored in the database.
- Error checked.
- Compiled object.
- Procedures may or may not use parameters (argument values).
- Default for Stored Procedures is to not return values. Most likely, you will be feeding your SP a value(s) but expecting no return value, unless there is an error.
- Explicit execution. EXECUTE command, along with specific SP name and optional parameters.
- Implicit execution using only SP name.
- Can not be executed or called from within a
- You can call a procedure as often as you like.
- Performance. The code is compiled only when created, meaning no need to require at run-time, unless you change the program (Stored Procedure).
- Modularity. When you find yourself writing very similar queries multiple times in your code, instead you can choose to write one stored procedure that – maybe with a parameter or two – can be called over and over from your code, with far less typing, potential typos, and future maintenance.
- Security. Reduces potential for various kinds of hacking, including “SQL query injection”.
For the bulk of this article, we’ll explore how to create a Stored Procedure (SP) specifically using PostgreSQL’s structured query language “PL/pgSQL”, which was developed by Oracle. Let’s begin with the syntax.
CREATE OR REPLACE PROCEDURE [proc name]([optional:parameters here])
IS [OR "AS"]
[optional declaration SECTION]
[execution; your actual "program"]
[optional: IN CASE OF error; error handling]
In terms of input and output to and from our Postgres Procedure, there are three kind of parameters:
- IN (only as input).
- IN OUT (in/out mode – both).
- OUT (output only).
Let’s begin with a simple table (named “technology”) in our PostgreSQL relational database system. We use this table to track various database and coding technologies used in our company.
Note: We are assuming the id/_tech field is an auto-incrementing keyed index.
CREATE OR REPLACE PROCEDURE ADD_USER
P_t_name IN technology.t_name%TYPE,
P_t_category IN technology.t_category%TYPE,
P_t_msg_error OUT VARCHAR2
-- No variable declarations at this time.
) VALUES (
WHEN OTHERS THEN
P_t_msg_error := SQLERRM;
Step by step explanation / analysis of the code above
OK, in case the above code is confusing for you, we’ll take it from the top.
CREATE: As you know, we use
CREATEto add objects to the database. Here, we recommend getting into the practice of adding
OR REPLACEto account for overwrites. In other words, odds are high you will want to make changes to this Stored Procedure (SP) from time to time.
ADD_USER: Next comes the naming of our SP. This is important because we need a name to use from our code to execute the Procedure. Here we named it
PARAMETERS: In parentheses immediately after the name of our SP are any parameters we chose to use with our SP. Note how we named all our parameter variables with a “P_” appended? This is required. Notice how two of our parameters are “IN” and one is “OUT”? The “IN” parameters are what get sent to the SP from our code and the “OUT” parameter is what our code sends back to our program. We’ll give an example of this later.
%TYPE: If we don’t know the parameter variable type, we can use a sort of placeholder called
%TYPEto declare the type of this parameter to be dynamic. This approach will reduce security and increase flexibility.
IS: Next we see
IS, which can also be
AS. No difference. They probably gave us this option for easier compatibility and ease of understanding if we are coming from other databases like SQL Server, MySQL, etc.
DECLARE: After that
DECLAREhas no arguments in its block. That’s because this is an optional area and for our purposes here, we don’t need to declare any variables.
BEGIN: This is how we tell Postgres the beginning of what we want executed against the database, all the way to the
ENDstatement at the bottom.
INSERT INTO: Here we are telling the database, “Hey we’re about to give you values to put into the following two columns (fields). Here’s the order they go in.
VALUES: This is to tell PostgreSQL what exact values to put in those fields we defined above. Notice the “P” in front of our two values? Scroll up a bit in the SP code to be reminded how, in the
PARAMETERSarea we named each parameter with a “P“.
COMMIT: Tell Postgres to actually follow the commands we gave it above this spot.
EXCEPTION: In this block, we tell PG what to do if there is an error.
WHEN OTHERS THEN: Here is the only part of this Stored Procedure that actually sends data back out to our code.
END: Marks the end of the
BEGINblock and end of Procedure execution.
Calling our Stored Procedure
Because Python works so well with Postgres, we’ll use this language for the example below.
from flask import Flask # popular python library
import psycopg2 # for database connection and dependent on Flask
app = Flask(__name__)
o_conn = psycopg2.connect(t_dsn)
o_cursor = o_conn.cursor()
# ^ usually no need to repeat this ^
t_results = o_cursor.fetchone()
# usually no need to repeat code below here
Here – if we assume the connection and cursor objects are not something we need to recreate and destroy over and over – we have three lines of code taking the place of Python, Java, PHP, Node, etc. code that builds the following:
) VALUES (
Note: The above SQL Statement doesn’t include the
TRY...COMMIT...EXCEPT block of error-checking code that may look like this:
except psycopg2.Error as e:
t_message = "Database error: " + e + "/n SQL: " + s
Now, consider writing those
INSERT INTO blocks of code over and over throughout your application but only changing what you send into those two fields? Do you see how much time and potential for typos you can save by using Stored Procedures instead? Of course here, we didn’t make the number of inserted fields dynamic. Let’s save that for a future article, eh?
- Declarations section can be empty.
- Stored Procedures that do not have parameters (arguments) are called “static”.
- Stored Procedures that use parameter values are called “dynamic”.
Here we learned why and how to create a Stored Procedure in Postgres to give us more efficiency, power, modularity, security, and ease. We also looked at how to call a Stored Procedure from Python.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started