SQL Programming Overview

In the article SQL Programming, we discussed creating the “Hello, World!” function in PostgreSQL, MySQL, and Oracle. In this article, we take “Hello, World!” one step farther and introduce the parts of a function and how to pass parameters to a function.

This article uses PostgreSQL’s PL/pgSQL procedural programming language, but with a little massaging, these functions can be used in any SQL-compliant database. Most of that “massaging” can be seen in the SQL Programming article.

Parts of a Function

Figure 1 shows a “Hello, World!” function that is slightly more complicated than those covered in the previous article. It also clearly illustrates the three main parts every function has.

First, we see the function declaration: CREATE OR REPLACE FUNCTION hello_world() RETURNS TEXT. This names the new (or replacement) function, declares its argument types between parentheses (in this case, there are none), and states the type of the returned data (TEXT, here). The “code block” encompasses the second and third parts of the function.

The second part is the variable declaration section, which begins with the DECLARE keyword. In this part, all the variables that will be used in the function must be declared, along with their type. Variables declared here may be assigned default values. For functions that do not use variables, the DECLARE keyword can be omitted, but it is best to think of it as still being part of the function, even though it is empty.

The third part of the function is the main body. This part is always surrounded by the BEGIN and END keywords, and it contains the code that will be run when the function is called. If the function is supposed to return a value, as directed by the RETURNS keyword on the CREATE FUNCTION line, it must be returned in this part of the function. It must also be of the same type that was specified after the RETURNS keyword.

{qbapagebreak title=SQL Functions: Passing Parameters}

Passing Parameters

Many functions require some input parameters. Usually, these will direct the function to look in a specific place for data, or the function will manipulate the parameters directly to produce an output. Figure 2 shows a simple example of a function that concatenates two text strings.

This function, which we have called concatenate(), takes two text strings as input and returns them as a single, concatenated text string. Note that variable types, but not variable names, were specified on the CREATE FUNCTION line. The variables passed into the function were referenced as $1 and $2 in the DECLARE section, instead. Also, rather than declaring the extra variables text1 and text2, $1 and $2 may be referenced directly from the function body, as shown in the RETURN line in Figure 3.

In Figure 3, it is also shown in the SELECT command that the result of a function call may be used directly as the input parameter for another function call, in this case creating the word “highlighter” from “high”, “light”, and “er”.

Note that PostgreSQL only allows input variables to be passed into a function. Oracle’s PL/SQL language, on the other hand, also allows output variables and bi-directional variables to be passed. These are variables that can be modified “in place” within a function, and when the function completes, the variables that were passed as parameters will have been altered accordingly.