websql

23 Working with Stored Procedures

Learning Objectives

:

Describe what stored procedures are and why they are used.

Describe the tradeoffs of using stored procedures.

Use the CALL statement to run a stored procedure in PostgreSQL.

Describe the basic syntax for creating a stored procedure in PostgreSQL, including IN, OUT, and INOUT parameters.

Use the RETURNING clause to capture an auto-generated primary key from within a procedure.

23.1 Understanding Stored Procedures

Most of the SQL statements that we've used thus far are simple in that they use a single statement against one or more tables. Not all operations are that simple. Often, multiple statements will be needed to perform a complete operation. For example, consider the following scenario:

To catalog a newly acquired work of art, checks must be made to determine whether the artist is already recorded in the artist table.
If the artist is already recorded, the new work needs to be linked directly to that artist's existing artist_id.
If the artist is not yet recorded, a new row needs to be added to the artist table first, and the new work then needs to be linked to the artist_id that was just created.
The registrar needs to be notified once the work has been fully catalogued and linked to both its artist and its museum.

This is obviously not a complete example, and it is even beyond the scope of the example tables that we have been using in this book, but it will suffice to help make a point. Performing this process requires many SQL statements against many tables. In addition, the exact SQL statements that need to be performed and their order are not fixed; they can (and will) vary according to whether the artist is already on file or not.

How would you write this code? You could write each of the SQL statements individually and execute other statements conditionally based on the result. You'd have to do this every time this processing was needed (and in every application that needed it).

You could create a stored procedure. Stored procedures are simply collections of one or more SQL statements saved for future use. You can think of them as batch files, although in truth they are more than that.

There's a Lot More to It. Stored procedures are complex, and full coverage of the subject requires more space than can be allocated here. Truthfully, there are entire books on the subject. This lesson will not teach you all you need to know about stored procedures. Rather, it is intended simply to introduce the subject so that you are familiar with what they are and what they can do. The examples in this lesson use PostgreSQL, the DBMS used throughout this book.

23.2 Understanding Why to Use Stored Procedures

Now that you know what stored procedures are, why use them? There are lots of reasons, but here are the primary ones:

To simplify complex operations (as seen in the previous example) by encapsulating processes into a single easy-to-use unit.
To ensure data consistency by not requiring that a series of steps be created over and over. If all developers and applications use the same stored procedure, then the same code will be used by all.
To prevent errors; this is an extension of the preceding reason. The more steps that need to be performed, the more likely it is that errors will be introduced. Preventing errors ensures data consistency.
To simplify change management. If tables, column names, or business logic (or just about anything) changes, then only the stored procedure code needs to be updated, and no one else will even need to be aware that changes were made.
To ensure security; this is an extension of the preceding reason. Restricting access to underlying data via stored procedures reduces the chance of data corruption (unintentional or otherwise).
To do less work to process the command. Because stored procedures are usually stored in a compiled form, the DBMS has to do less work. This results in improved performance.
To write code that is more powerful and flexible. There are SQL language elements and features that are available only within single requests. Stored procedures can use them for this reason.

In other words, there are three primary benefits: simplicity, security, and performance. Obviously, all are extremely important. Before you run off to turn all your SQL code into stored procedures, here's the downside:

Stored procedure syntax varies dramatically from one DBMS to the next. In fact, it is close to impossible to write truly portable stored procedures. Having said that, the stored procedure calls themselves (their names and how data is passed to them) can be kept relatively portable so that if you need to change to another DBMS, at least your client application code may not need changing.
Stored procedures tend to be more complex to write than basic SQL statements, and writing them requires a greater degree of skill and experience. As a result, many database administrators restrict stored procedure creation rights as a security measure (primarily due to the previous bullet item).

Nonetheless, stored procedures are very useful and should be used. In fact, most DBMSs come with all sorts of stored procedures that are used for database and table management. Refer to your DBMS documentation for more information on these.

Can't Write Them? You Can Still Use Them. Most DBMSs distinguish the security and access needed to write stored procedures from the security and access needed to execute them. This is a good thing; even if you can't (or don't want to) write your own stored procedures, you can still execute them when appropriate.

23.3 Executing Stored Procedures

Stored procedures are executed far more often than they are written, so we'll start there. In PostgreSQL, the SQL statement used to execute a stored procedure is CALL. (This is different from many other DBMSs, which use EXECUTE or EXEC — in PostgreSQL, EXECUTE means something else entirely: running a previously prepared statement.) CALL takes the name of the stored procedure and any parameters that need to be passed to it. Take a look at this example (you cannot actually run it yet, because the stored procedure add_new_work does not exist):

CALL add_new_work(500, 3, 'Girl with a Fan', 'Impressionist');

Here a stored procedure named add_new_work is executed; it adds a new work to the work table. add_new_work takes four parameters: the artist ID (the primary key from the artist table), the museum ID (the primary key from the museum table), the work's name, and its style. These four parameters match four expected parameters within the stored procedure (defined as part of the stored procedure itself). The stored procedure adds a new row to the work table and assigns these passed values to the appropriate columns.

In the work table, you'll notice that another column needs a value — the work_id column, which is the table's primary key. Why was this value not passed as a parameter to the stored procedure? To ensure that IDs are generated properly, it is safer to have that process automated (and not rely on end users). That is why work_id is defined as an auto-incrementing column, and the stored procedure never has to be told what value to use. This is what this stored procedure does:

It validates the passed data, ensuring that all four parameters have values.
It inserts the new work into the work table, letting the database generate a unique work_id automatically and storing the passed data in the appropriate columns.

This is the basic form of stored procedure execution. PostgreSQL also supports several other useful features:

Parameters with default values, assumed if a parameter is not provided
Named parameters, passed as parameter := value pairs rather than strictly by position
OUT and INOUT parameters, allowing the stored procedure to pass a value back to the caller
Return values captured directly from an INSERT, UPDATE, or DELETE using RETURNING

23.4 Creating Stored Procedures

As already explained, writing a stored procedure is not trivial. To give you a taste for what is involved, let's look at a simple example — a stored procedure that counts the number of museums in the database that have a website on file.

CREATE PROCEDURE museum_website_count(INOUT list_count INTEGER)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT COUNT(*) INTO list_count
    FROM museum
    WHERE url IS NOT NULL;
END;
$$;

This stored procedure takes a single parameter named list_count. Instead of only passing a value into the stored procedure, this parameter also passes a value back out of it. The keyword INOUT is used to specify this behavior. PostgreSQL supports parameters of types IN (the default — those passed into a stored procedure), OUT (those passed back out of a stored procedure), and INOUT (those used to pass values both in and out, as used here). The stored procedure code itself is enclosed within a BEGIN and END block, written in the plpgsql procedural language, and here a simple SELECT is performed to count the museums with a website on file. The result is stored directly into list_count using the INTO clause.

To invoke this example, you pass a placeholder value (commonly NULL) for the INOUT parameter, since you don't yet know what it should be:

CALL museum_website_count(NULL);

PostgreSQL runs the procedure and returns list_count as a one-row result, now containing the count.

Functions vs. Procedures in PostgreSQL. In practice, when all you need to do is compute and return a single value like a count, most PostgreSQL developers would reach for a FUNCTION instead of a PROCEDURE. A procedure is generally best suited for a task that performs actions (like inserting rows) rather than one that simply computes and returns a value. Here is the same count written as a function instead:
CREATE FUNCTION museum_website_count() RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM museum
    WHERE url IS NOT NULL;
    RETURN v_count;
END;
$$;
You call a function with SELECT rather than CALL:
SELECT museum_website_count();
Both approaches are valid PostgreSQL, but seeing them side by side helps clarify the difference between the two: procedures (called with CALL) versus functions (called with SELECT).

Here's another example, this time to insert a new work into the work table. It demonstrates some useful stored procedure techniques that are especially idiomatic in PostgreSQL. For this example, assume the work table has also been given an additional date_catalogued column, used to record when each work was added to the database:

CREATE PROCEDURE new_work(
    p_artist_id INTEGER,
    INOUT p_work_id INTEGER DEFAULT NULL,
    p_date_catalogued DATE DEFAULT CURRENT_DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO work (artist_id, date_catalogued)
    VALUES (p_artist_id, p_date_catalogued)
    RETURNING work_id INTO p_work_id;
END;
$$;

This stored procedure creates a new work in the work table. It takes a single required parameter — the ID of the artist who created the work — plus an INOUT parameter, p_work_id, used to hand the generated work_id back to the caller. The work_id and date_catalogued columns are populated automatically: work_id because it is an auto-incrementing column, and date_catalogued because it defaults to CURRENT_DATE (PostgreSQL's function for today's date) if no other date is supplied. The INSERT statement uses the RETURNING ... INTO clause to capture the automatically generated work_id directly into p_work_id, so the caller finds out the new work's ID without having to run a separate query for it. Notice that the parameters are prefixed with p_ — this is a common PostgreSQL convention that keeps parameter names from colliding with column names of the same name inside the procedure body, which can otherwise cause "ambiguous" errors.

To invoke this example:

CALL new_work(500, NULL);

Because p_work_id defaults to NULL and p_date_catalogued defaults to CURRENT_DATE, you only need to supply the artist_id and a placeholder for the INOUT parameter. PostgreSQL runs the procedure and returns the newly generated work_id as a one-row result.

Comment Your Code. All code should be commented, and stored procedures are no different. Adding comments will not affect performance at all, so there is no downside here (other than the time it takes to write them). The benefits are numerous and include making it easier for others (and yourself) to understand the code and safer to make changes at a later date. In PostgreSQL, single-line comments are preceded by -- (two hyphens), and multi-line comments are enclosed in /* ... */.

As you can see, with stored procedures there are often many different ways to accomplish the same task. Whether you reach for a procedure or a function, and how you capture generated values, will often be dictated by which feature of PostgreSQL best fits what you're trying to do.

23.5 Summary

In this lesson, you learned what stored procedures are and why they are used. You also learned the basics of stored procedure execution and creation syntax in PostgreSQL — including CALL, IN/OUT/INOUT parameters, and the RETURNING clause — and you saw some of the ways these can be used. Using stored procedures is a really important topic, and one that is far beyond the scope of one lesson. Refer to the PostgreSQL documentation for more details.