Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page


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 called 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.

Example 11-11. Declaring a PL/pgSQL variable

variable_name data_type
 [ := 

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

 AS '
     -- Declare an integer.
    subject_id INTEGER;
     -- Declare a variable length character.
    book_title VARCHAR(10);
      -- Declare a floating point number.
    book_price FLOAT;

' 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:

 [ NOT NULL ] [ { DEFAULT | := } 

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 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'';
  return letter;
' LANGUAGE 'plpgsql';


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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire