Variables are used within PL/pgSQL code to store modifiable data of an explicitly stated
type. All variables that you will be using within a code block must be declared under the
DECLARE keyword. If a variable is not initialized to a default value when
it is declared, its value will default to the SQL NULL type.
Note
As
you will read later on in the section titled Controlling Program Flow, there
is a type of statement known as the FOR loop that initializes a variable
used for iteration. The FOR loop’s iteration variable does not have to be
pre-declared in the DECLARE section for the block the loop is located
within; hence, the FOR loop is the only exception to the rule that all
PL/pgSQL variables must be declared at the beginning of the block they are located
within.
Variables in PL/pgSQL can be represented by any of SQL’s standard data types, such as an
integer or char. In addition to SQL data types,
PL/pgSQL also provides the additional RECORD data type, which is designed
to allow you to store row information without specifying the columns that will be supplied
when data is inserted into the variable. More information on using RECORD
data types is provided later in this chapter. For further information on standard SQL data
types, see the section titled Data Types in Chapter 3; the following is a brief list of commonly used data types in
PL/pgSQL:
booleantextcharintegerdouble precisiondatetime
For variables to be available to the code within a PL/pgSQL code block, they must be
declared in the declarations section of the block, which is denoted by the DECLARE keyword at the beginning of the block. Variables declared in a block will
be available to all sub-blocks within it, but remember that (as mentioned in the section
titled Language Structure earlier in this chapter) variables declared within
a sub-block are destroyed when that sub-block ends, and are not available for use by their
parent blocks. The format for declaring a variable is shown in Example 11-11.
As you can see by Example 11-11, you declare a variable by providing its name and type (in that order), then end the declaration with a semicolon.
Example 11-12 shows the declaration of a variable of the
integer data type, a variable of the varchar data type
(the value in parentheses denotes that this variable type holds ten characters), and a
variable of the float data type.
Example 11-12. Variable Declarations
CREATE FUNCTION identifier (arguments) RETURNS type AS ' DECLARE - - Declare an integer. subject_id integer; - - Declare a variable length character. book_title varchar(10); - - Declare a floating point number. book_price float; BEGIN statements END; ' LANGUAGE 'plpgsql';
You may also specify additional options for a variable. Adding the CONSTANT keyword indicates that a variable will be created as a constant.
Constants are discussed later in this section.
The NOT NULL keywords indicate that a variable cannot be set as
NULL. A variable declared as NOT NULL will cause a
run-time error if it is set to NULL within the code block. Due to the fact
that all variables are set to NULL when declared without a default value, a
default value must be provided for any variable that is declared as NOT
NULL.
The DEFAULT keyword allows you to provide a default value for a
variable. Alternatively, you can use the := operator without specifying the
DEFAULT keyword, to the same effect.
The following illustrates the use of these options within a variable declaration:
variable_name [ CONSTANT ] data_type [ NOT NULL ] [ { DEFAULT | := } value ];
Example 11-13 shows the declaration of a constant
variable with the default value of 5, the declaration of a variable with the value of 10 which
cannot be set to NULL, and the declaration of a character with the default
value of one a.
Example 11-13. Using variable declaration options
CREATE FUNCTION example_function ( ) RETURNS text AS '
DECLARE
- - Declare a constant integer with a
- - default value of 5.
five CONSTANT integer := 5;
- - Declare an integer with a default
- - value of 100 that cannot be NULL.
ten integer NOT NULL := 10;
- - Declare a character with
- - a default value of "a".
letter char DEFAULT ''a'';
BEGIN
- - This just returns the letter, and
- - ends the function.
return letter;
END;
' LANGUAGE 'plpgsql';Note
The
RENAME keyword covered in online documentation for PL/pgSQL, which is
intended to rename existing variables to new names, does not work at all in PL/pgSQL (as of
PostgreSQL 7.1.x). The use of this keyword on an existing variable indiscriminately causes a
parsing error. It is therefore not recommended, nor documented in this chapter.
Variable assignment is done with PL/pgSQL’s assignment operator (:=),
in the form of left_variable
:=
right_variable, in which the value of the right
variable is assigned to the left variable. Also valid is left_variable
:=
expression, which assigns the left-hand variable the
value of the expression on the right side of the assignment operator.
Variables can be assigned default values within the declaration section of a PL/pgSQL
code block. This is known as default value assignment, and is done by
using the assignment operator (:=) on the same line as the variable’s
declaration. This topic is discussed in more detail later in this section, but Example 11-14 provides a quick demonstration.
Example 11-14. Default value assignment
CREATE FUNCTION identifier (arguments) RETURNS type AS ' DECLARE an_integer int4 := 10; BEGIN statement; [...] END; ' LANGUAGE 'plpgsql';
It is also possible to use a SELECT INTO statement to assign variables
the results of queries. This use of SELECT INTO is different from the SQL
command SELECT INTO, which assigns the results of a query to a new
table.
Note
To assign the results of a query to a new table within PL/pgSQL, use the alternative SQL
syntax CREATE TABLE AS SELECT.
SELECT INTO is primarily used to assign row and record information to
variables declared as %ROWTYPE or RECORD types. To use
SELECT INTO with a normal variable, the variable in question must be the
same type as the column you reference in the SQL SELECT statement provided.
The syntax of SELECT INTO statement is shown in the following
syntax:
CREATE FUNCTION identifier (arguments) RETURNS type AS ' DECLARE statement; BEGIN SELECT INTO target_variable [, ...] target_column [, ...] select_clauses; END; ' LANGUAGE 'plpgsql';
In this syntax, target_variable is the name of a
variable that is being populated with values, and select_clauses consists of any supported SQL SELECT
clauses that would ordinarily follow the target column list in a SELECT
statement.
Example 11-15 shows a simple function that
demonstrates the use of a SELECT INTO statement. The ALIAS keyword is described in the section titled Argument Variables, later in this chapter. See the section titled Controlling Program Flow for examples of using SELECT INTO
with RECORD and %ROWTYPE variables.
Example 11-15. Using the SELECT INTO statement
CREATE FUNCTION get_customer_id (text,text) RETURNS integer AS '
DECLARE
- - Declare aliases for user input.
l_name ALIAS FOR $1;
f_name ALIAS FOR $2;
- - Declare a variable to hold the customer ID number.
customer_id integer;
BEGIN
- - Retrieve the customer ID number of the customer whose first and last
- - name match the values supplied as function arguments.
SELECT INTO customer_id id FROM customers
WHERE last_name = l_name AND first_name = f_name;
- - Return the ID number.
RETURN customer_id;
END;
' LANGUAGE 'plpgsql';Example 11-16 shows the results of the
get_customer_id( ) function when passed the arguments
Jackson and Annie. The number returned is the
correct ID number for Annie Jackson in the customers table.
Example 11-16. Result of the get_customer_id( ) function
booktown=# SELECT get_customer_id('Jackson','Annie');
get_customer_id
-----------------
107
(1 row)If you wish to assign multiple column values to multiple variables, you may do so by
using two comma-delimited groups of variable names and column names, separated from one
another by white space. Example 11-17 creates
essentially an inverse function to the get_customer_id( ) function created
in Example 11-15.
Example 11-17. Using SELECT INTO with multiple columns
CREATE FUNCTION get_customer_name (integer) RETURNS text AS '
DECLARE
- - Declare aliases for user input.
customer_id ALIAS FOR $1;
- - Declare variables to hold the customer name.
customer_fname text;
customer_lname text;
BEGIN
- - Retrieve the customer first and last name for the customer
- - whose ID matches the value supplied as a function argument.
SELECT INTO customer_fname, customer_lname
first_name, last_name
FROM customers WHERE id = customer_id;
- - Return the name.
RETURN customer_fname || '' '' || customer_lname;
END;
' LANGUAGE 'plpgsql';Example 11-18 shows the results of the
get_customer_name( ) function, when passed an argument of 107.
Example 11-18. Result of the get_customer_name( ) function
booktown=# SELECT get_customer_name(107);
get_customer_name
-------------------
Annie Jackson
(1 row)Use the special FOUND Boolean variable directly after a SELECT INTO statement to check whether or not the statement successfully inserted
a value into the specified variable. You can also use ISNULL or IS
NULL to find out if the specified variable is NULL after being
selected into (in most situations, this would mean the SELECT INTO
statement failed).
FOUND, IS NULL, and ISNULL should be used within a
conditional (IF/THEN) statement. PL/pgSQL’s conditional statements are
detailed in the Controlling Program Flow section of this chapter. Example 11-19 is a basic demonstration of how the
FOUND Boolean could be used with the get_customer_id( )
function.
Example 11-19. Using the FOUND boolean in get_customer_id( )
[...]
SELECT INTO customer_id id FROM customers
WHERE last_name = l_name AND first_name = f_name;
- - If a match could not be found, return -1 (another function calling
- - this function could then be made to interpret a -1 as an error.
IF NOT FOUND THEN
return -1;
END IF;
[...]Example 11-20 shows that get_customer_id( ) now returns a –1 value when passed the name
of a non-existent customer.
PL/pgSQL functions can accept argument variables of different types. Function arguments allow you to pass information from the user into the function that the function may require. Arguments greatly extend the possible uses of PL/pgSQL functions. User input generally provides a function with the data it will either operate on or use for operation. Users pass arguments to functions when the function is called by including them within parentheses, separated by commas.
Arguments must follow the argument list defined when the function is first created. Example 11-21 shows a pair of example function calls from psql.
Example 11-21. Function call examples
booktown=# SELECT get_author('John'); get_author -------------- John Worsley (1 row) booktown=# SELECT get_author(1111); get_author -------------- Ariel Denham (1 row)
Each function argument that is received by a function is incrementally assigned to an
identifier that begins with the dollar sign ($) and is labeled with the
argument number. The identifier $1 is used for the first argument, $2 is used for the second argument, and so forth. The maximum number of function
arguments that can be processed is sixteen, so the argument identifiers can range from
$1 to $16. Example 11-22 shows a function that doubles an integer
argument variable that is passed to it.
Example 11-22. Directly using argument variables
CREATE FUNCTION double_price (float) RETURNS float AS '
DECLARE
BEGIN
- - Return the argument variable multiplied by two.
return $1 * 2;
END;
' LANGUAGE 'plpgsql';Referencing arguments with the dollar sign and the argument’s order number can become confusing in functions that accept a large number of arguments. To help in functions where the ability to better distinguish argument variables from one another is needed (or just when you wish to use a more meaningful name for an argument variable), PL/pgSQL allows you to create variable aliases.
Aliases are created with the ALIAS keyword and give you the ability to
designate an alternate identifier to use when referencing argument variables. All aliases must
be declared in the declaration section of a block before they can be used (just like normal
variables). Example 11-23 shows the syntax of the ALIAS keyword.
Example 11-23. Syntax of the ALIAS keyword
CREATE FUNCTION function_identifier (arguments) RETURNS type AS ' DECLARE identifier ALIAS FOR $1; identifier ALIAS FOR $2; BEGIN [...] END; ' LANGUAGE 'plpgsql';
Example 11-24 creates a simple function to demonstrate
the use of aliases in a PL/pgSQL function. The triple_price( ) function
accepts a floating point number as the price and returns that number multiplied by
three.
Example 11-24. Using PL/pgSQL aliases
CREATE FUNCTION triple_price (float) RETURNS float AS '
DECLARE
- - Declare input_price as an alias for the argument variable
- - normally referenced with the $1 identifier.
input_price ALIAS FOR $1;
BEGIN
- - Return the input price multiplied by three.
RETURN input_price * 3;
END;
' LANGUAGE 'plpgsql';Now, if we use the triple_price function within a SQL SELECT statement in a client such as psql, we receive the
results shown in Example 11-25.
PL/pgSQL functions must return a value that matches the data type specified as their
return type in the CREATE FUNCTION command that created them. Values are
returned with a RETURN statement. A RETURN statement is
typically located at the end of a function, but will also often be located within an IF statement or other statement that directs the flow of the function. If a
function’s RETURN statement is located within one of these control
statements, you should still include a return statement at the end of the function (even if
the function is designed to never reach that last RETURN statement). The
syntax of a RETURN statement is shown in Example 11-26.
Example 11-26. Syntax of the RETURN statement
CREATE FUNCTION function_identifier (arguments) RETURNS type AS ' DECLARE declaration; [...] BEGIN statement; [...] RETURN { variable_name | value } END; ' LANGUAGE 'plpgsql';
For a demonstration of the RETURN statement, examine any PL/pgSQL
function example within this chapter.
PL/pgSQL provides variable attributes to assist you in working with
database objects. These attributes are %TYPE and %ROWTYPE. Use attributes to declare a variable to match the type of a database
object (using the %TYPE attribute) or to match the row structure of a row
(with the %ROWTYPE attribute). A variable should be declared using an
attribute when it will be used within the code block to hold values taken from a database
object. Knowledge of the database object’s type is not required when using attributes to
declare variables. If an object’s type changes in the future, your variable’s type will
automatically change to that data type without any extra code.
The %TYPE attribute is used to declare a variable with the data type
of a referenced database object (most commonly a table column). The format for declaring a
variable in this manner is shown in Example 11-27.
Example 11-28 shows the code for a function that
uses %TYPE to store the last name of an author. This function uses string
concatenation with the concatenation operator (||), which is documented in
a later section. The use of the SELECT INTO statement was discussed
earlier in this chapter.
Focus on the use of the %TYPE attribute in Example 11-28. Essentially, a variable is declared as being
the same type as a column within the authors table. SELECT is then used to find a row with a first_name field that
matches the name the user passed to the function. The SELECT statement
retrieves the value of that row’s last_name column and inserts it into the
l_name variable. An example of the user’s input to the function is shown
right after Example 11-28, in Example 11-29, and more examples of user input can be
found later in this chapter.
Example 11-28. Using the %TYPE attribute
CREATE FUNCTION get_author (text) RETURNS text AS '
DECLARE
- - Declare an alias for the function argument,
- - which should be the first name of an author.
f_name ALIAS FOR $1;
- - Declare a variable with the same type as
- - the last_name field of the authors table.
l_name authors.last_name%TYPE;
BEGIN
- - Retrieve the last name of an author from the
- - authors table whose first name matches the
- - argument received by the function, and
- - insert it into the l_name variable.
SELECT INTO l_name last_name FROM authors WHERE first_name = f_name;
- - Return the first name and last name, separated
- - by a space.
return f_name || '' '' || l_name;
END;
' LANGUAGE 'plpgsql';Example 11-29 shows the results of using
the get_author( ) function.
%ROWTYPE is used to declare a PL/pgSQL record variable with the same
structure as the rows in a table you specify. It is similar to the RECORD
data type, but a variable declared with %ROWTYPE will have the exact
structure of a table’s row, whereas a RECORD variable is not structured
and will accept a row from any table.
Example 11-30 overloads the get_author(
) function that was created in Example 11-28
to accomplish a similar goal. Notice, though, that this new version of get_author(
) accepts an argument of type integer rather than text, and checks for the author by comparing their id against
the passed integer argument.
Notice also that this function is implemented using a variable declared with %ROWTYPE. The use of %ROWTYPE to accomplish a simple task such
as this may make it seem overly complicated, but as you learn more about PL/pgSQL, the
importance of %ROWTYPE will become more apparent.
The use of the dot (.) within the found_author
variable in Example 11-30 references a named field
value in found_author.
Example 11-30. Using the %ROWTYPE attribute
CREATE FUNCTION get_author (integer) RETURNS text AS '
DECLARE
- - Declare an alias for the function argument,
- - which should be the id of the author.
author_id ALIAS FOR $1;
- - Declare a variable that uses the structure of
- - the authors table.
found_author authors%ROWTYPE;
BEGIN
- - Retrieve a row of author information for
- - the author whose id number matches
- - the argument received by the function.
SELECT INTO found_author * FROM authors WHERE id = author_id;
- - Return the first name and last name, separated by a space.
RETURN found_author.first_name || '' '' || found_author.last_name;
END;
' LANGUAGE 'plpgsql';Observe the use of the asterisk (*) for the column list in Example 11-30. Since found_author is
declared with the %ROWTYPE attribute on the authors
table, it is created with the same data structure as the authors table.
The asterisk can therefore be used to populate the found_author variable
with each column value selected from the SELECT INTO statement in Example 11-15.
Concatenation is the process of combining two (or more) strings together to produce another string. It is a standard operation built into PostgreSQL, and may therefore be used directly on variables within a PL/pgSQL function. When working with several variables containing character data, it is an irreplaceable formatting tool.
Concatenation can only be used with character strings. Strings are concatenated by
placing the concatenation operator (||) between two or more character
strings (string literal or a character string variable) that you wish to be combined. This can
be used to combine two strings together to form a compound word, and to combine multiple
strings together to form complex character string combinations.
Concatenation can only be used in situations where your function requires a string value, such as when a string must be returned (as shown in Example 11-32), or when you are assigning a new value to a string variable (as shown in Example 11-33).
Example 11-32. Returning a concatenated string
CREATE FUNCTION compound_word(text, text) RETURNS text AS '
DECLARE
- - Define aliases for function arguments.
word1 ALIAS FOR $1;
word2 ALIAS FOR $2;
BEGIN
- - Return the resulting joined words.
RETURN word1 || word2;
END;
' LANGUAGE 'plpgsql';When the words break and fast are passed as
arguments to the compound_word( ) function, the function returns
breakfast as the concatenated string:
booktown=# SELECT compound_word('break', 'fast');
compound_word
---------------
breakfast
(1 row)Example 11-33. Assigning a concatenated value to a string
CREATE FUNCTION title_and_author (text, text) RETURNS text AS '
DECLARE
- - Declare aliases for the two function arguments.
title ALIAS for $1;
author ALIAS for $2;
- - Declare a text variable to hold the string result
- - of the concatenation.
result text;
BEGIN
- - Combine the title variable and the author
- - variable together, placing a comma and the
- - word by between them.
result := title || '', by '' || author;
- - Return the resulting string.
return result;
END;
' language 'plpgsql';If you pass the strings Practical PostgreSQL and Command Prompt, Inc. to the function created in Example 11-33, the function returns Practical PostgreSQL, by Command Prompt, Inc.:
booktown=# SELECT title_and_author('Practical PostgreSQL','Command Prompt, Inc.');
title_and_author
-----------------------------------------------
Practical PostgreSQL, by Command Prompt, Inc.
(1 row)