Unemployed Developer

There are two schools of thought regarding stored procedures. On side for them, you’ll hear about the virtues of cross-language functionality (any language with a Postgres database wrapper can run the functions, saving re-writing in separate libraries), they’re fast (precompiled), they operate in transactions and you can specify varying amounts of permissions. On the side against them, you’ll hear about the difficulty debugging them due to caching issues, testing can be a pain in the butt and deployment isn’t all that fun when you’ve got clustered databases to worry about. I believe there is a use for stored procedures, and, when used correctly, can make for some modular goodness. Firstly, let’s take a look at a sample stored procedure.

CREATE FUNCTION public.populate_client(VARCHAR,VARCHAR)
    RETURNS INTEGER AS $$
DECLARE
    n_firstname ALIAS FOR $1;
    n_lastname ALIAS FOR $2;
    a_client_id INTEGER;
BEGIN
    a_client_id := NEXTVAL('public.client_seq');
    INSERT INTO public.client (client_id, firstname, lastname)
        VALUES (a_client_id, n_firstname, n_lastname);
    INSERT INTO public.client_rating (client_id, rating)
        VALUES (a_client_id, 5);

    RETURN a_client_id;
END;

$$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

So what’s going on here? Let’s take a look at the first line:

CREATE FUNCTION public.populate_client(VARCHAR,VARCHAR)
    RETURNS INTEGER AS $$

This is the function declaration. The VARCHARs inside the brackets indicate the type of parameters. RETURNS INTEGER is the return type. Use VOID instead of INTEGER if you’re not returning anything. You can even return record sets. This involves creating a ROWTYPE.

DECLARE
    n_firstname ALIAS FOR $1;
    n_lastname ALIAS FOR $2;
    a_client_id INTEGER;

This is the variable declaration section, if you’re using any. The ALIAS keyword allows you to name the parameters in order for easier readability. Otherwise you’re using Perl-esque $1, $2 notation, and that gets messy fast.

Your function logic goes between the BEGIN and END keywords. Since SQL is not Turing-complete, PL/psql lets you make use of loops, if-else trees and even exceptions.

$$ LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;

This line ends the function declaration. You don’t need the VOLATILE SECURITY DEFINER keywords, but they are helpful. They determine what a stored procedure is allowed to do. The VOLATILE keyword signifies the volatility of a stored procedure. VOLATILE means it can modify the database and can return different results on the same arguments (could be due to changed data). STABLE means the function won’t modify the database and will return the same results on the same arguments within a single statement. IMMUTABLE will not modify the database and will return the same results with the same arguments forever. Usually your stored procedures will be VOLATILE if they do anything in the database.

SECURITY DEFINER keywords define, well, the security. The DEFINER keyword means the function will run with the same privileges as its creator. SECURITY INVOKER means the function will run with the same privileges as its caller. In both cases, you’ll need to make sure the tables the function accesses or modifies has the necessary permissions for the user that runs or creates the function.

Don’t forget to set appropriate permissions for the users you want to run this function after you create it. That’s it for now. Look in the future for returning ROWTYPEs, usage in queries and handling exceptions.