PostgreSQL does not support stored procedures in the sense that a database such as Oracle does, but it does support stored functions. In this post, I look at a few tactics that can make the use of a stored function in PostgreSQL (stored function and its calling code both written in PL/pgSQL) feel like using a stored procedure. These simple approaches allow developers to use PostgreSQL stored functions in a manner that is more consistent with use of stored procedures.
Stored procedures and stored functions are very similar and, in fact, I’ve often heard the term “stored procedure” used interchangeably for stored procedures and for stored functions. For purposes of this post, the essential differences between the two can be summarized as:
- Functions are created with the FUNCTION keyword and procedures are created with the PROCEDURE keyword.
- Stored procedures do not return a value, but stored functions return a single value.
- The stored function’s return value can be used in SELECT statements.
Because PostgreSQL PL/pgSQL only supports stored functions, the defined functions need to declare a return type. Fortunately, in the case of our emulated “stored procedure,” we can declare void as the return type. This is demonstrated in the code listing below for a “Hello World” implementation written in PL/pgSQL.
CREATE OR REPLACE FUNCTION helloWorld(name text) RETURNS void AS $helloWorld$
DECLARE
BEGIN
RAISE LOG 'Hello, %', name;
END;
$helloWorld$ LANGUAGE plpgsql;With a PostgreSQL stored function with void return type written, we now can invoke it from a client. In this case, I will look at three approaches for calling the stored function from other PL/pgSQL code.
PL/pgSQL: Invoke Function Via SELECT Statement
One approach for calling the stored function in PL/pgSQL code is to use SELECT INTO. The most obvious disadvantage is that, in the case of a procedure-like function returning void, nothing useful being selected and so the variable being selected into must be ignored anyway. The next code listing demonstrates using SELECT INTO to invoke the procedure-like function. The variable in this example, called “dumped”, will not have anything useful selected into it, but this statement successfully invoke the stored function. Besides the line shown here, I also need a line in the DECLARE section to declare the “dumped” variable.
SELECT INTO dumped helloWorld('Dustin');PL/pgSQL: Invoke Function Via Variable Assignment
The PL/pgSQL assignment operator provides another way to invoke the procedure-like stored function. As with the previous example, this approach requires a variable (“ignored”) be declared in the DECLARE section and then that variable is assigned the result of the function that returns void, making it effectively a throw-away variable as well.
ignored := helloWorld('Dustin');PL/pgSQL: Use PERFORM to Explicitly Ignore Returned Value
The PL/pgSQL command PERFORM provides some syntactical advantages when invoking procedure-like stored functions. This command does not require a PL/pgSQL variable to be declared. This saves the line to declare the variable and avoids the pretense of setting a variable that is never really set. It’s really just a shortcut for SELECT, but it’s syntactically sweeter and makes for more readable code because code maintainers don’t have to figure out that a statement that appears to make an assignment actually does not do so.
PERFORM helloWorld('Dustin');Conclusion
Although PostgreSQL only supports stored “functions” (and not stored “procedures”), it provides syntax that allows for functions to take on procedure-like qualities. BY allowing stored functions to return void and be to called from PL/pgSQL code via PERFORM that expects no result value, PostgreSQL allows client code to appear as if it is invoking a stored procedure rather than a stored function.
| Reference: | Procedure-Like Functions in PostgreSQL PL/pgSQL from our JCG partner Dustin Marx at the Inspired by Actual Events blog. |
