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.