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.

Next Page: SQL Functions: Passing Parameters