/ ID: 766 Title: Postgres Stored Procedures with Input and Output Parameters SQL 766 Meta Description: This article explores the use of the Postgres Stored Procedure using input and output parameters with SQL. Code samples included. Meta Keywords: Postgres Stored Procedures Input Output Parameters SQL Author: orkb Template: Unstructured Tutorial Categories: PostgreSQL Tags: PostgreSQL, Stored Procedures, SQL, Python Status: Published /

Introduction

In this tutorial, we learn to create Postgres Stored Procedures, including use of input and output parameters, in the SQL we write. 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?

Prerequisites

• Basic understanding of how to script SQL, either with PostgreSQL (or MS SQL Server, Oracle, MySQL, etc.) alone, using the free PG Admin tool or some other relational db administration tool, or even by using a scripting (compiled or not, object-oriented or not) language (like Java, C#, ASP.Net, VB.Net, PHP, Javascript, Python, Node, Ruby, etc.) that provides a connection to your database as well as a method for sending it T-SQL statements, to query or make changes to your data.
• Knowledge of the use of the most basic SQL commands, including SELECT, FROM, and INSERT.
• Optional: Knowledge of Python.

What is a "Stored Procedure" and how does it work?

Some features of Stored Procedures:

1. We can refer to SPs as a "named program" that is stored in the database.
2. Error checked.
3. Compiled object.
4. Procedures may or may not use parameters (argument values).
5. 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.
6. Execution:
7. Explicit execution. EXECUTE command, along with specific SP name and optional parameters.
8. Implicite execution using only SP name.
9. Can not be executed or called from within a SELECT.
10. You can call a procedure as often as you like.

Why?

Purposes:

• 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".

How?

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.

Syntax

SQL CREATE OR REPLACE PROCEDURE [proc name]([optional:parameters here]) IS [or "AS"] DECLARE [optional] [optional declaration section] BEGIN [execution; your actual "program"] EXCEPTION [optional] [optional: in case of error; error handling] END;

About Parameters

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).

Use Case

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.

code public.technology id_tech t_name t_category

Note: We are assuming the id/_tech field is an auto-incrementing keyed index.

| id_tech | t_name | t_category | |---------|------------|------------| | 0 | Oracle | RDB | | 1 | Mongo | NoSQL | | 2 | MySQL | RDB | | 3 | PostgreSQL | RDB | | 4 | Python | Language | | 5 | PHP | Language |

``` SQL 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 ) IS

DECLARE -- No variable declarations at this time.

BEGIN INSERT INTO technology ( t_name , t_category ) VALUES ( P_t_name , P_t_category COMMIT; EXCEPTION WHEN OTHERS THEN P_t_msg_error := SQLERRM; END ADD_USER; ```

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 CREATE to add objects to the database. Here, we recommend getting into the practice of adding OR REPLACE to 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 ADD_USER.
• 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 %TYPE to 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 DECLARE has 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 END statement 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 PARAMETERS area 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 BEGIN block 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.

``` PYTHON 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 ^

o_cursor.callproc('ADD_USER', ('Node.js','Language')) t_results = o_cursor.fetchone() o_cursor.close()

usually no need to repeat code below here

o_conn.close() ```

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:

SQL INSERT INTO technology ( t_name , t_category ) VALUES ( 'Node.js' , 'Language' COMMIT;

Note: The above SQL Statement doesn't include the TRY...COMMIT...EXCEPT block of error-checking code that may look like this:

PYTHON db_cursor.execute(s) try: db_conn.commit() 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?

Some tidbits

• 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".

Conclusion

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.