Database Reference
In-Depth Information
We can see that the
total
variable is declared with its
INTEGER
data type before it
stores the result returned to it.
The following is the general syntax for declaring a variable:
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT
NULL ] [ { DEFAULT | := } expression
Adding a variable with the
CONSTANT
keyword makes sure that the variable's values
will remain constant during the execution of the block.
The
COLLATE
keyword deines the collation to be used for variable.
Variables can be declared with a default value; if they are not given a default value,
they are assigned a null value. You can also use the
:=
operator that carries the same
meaning as using the
DEFAULT
keyword.
NOT NULL
is the case where you do not want to set the value of a variable as null,
and doing so in the block section can lead to a runtime error.
As a good programming practice, a variable declared
as
NOT NULL
should be given a default value.
It should be remembered that each time a block is executed, default values are
evaluated and assigned so. This is further explained with the help of a PL/pgSQL
function that contains variables using the
DEFAULT
,
CONSTANT
, and
NOT NULL
options.
The
func_declare()
function in the following example shows the declaration of
a
CONSTANT
variable with its default value as
10
. The declaration of a variable with
NOT NULL
is also initialized with a default value and the declaration of a character
variable with the
DEFAULT
value:
warehouse_db=# CREATE OR REPLACE FUNCTION func_declare()
RETURNS text AS $$
DECLARE
-- Variable store declared not null with a default value.
nanvar VARCHAR NOT NULL := 'notnull text';
-- Declaring an integer to hold integer constant.
digit CONSTANT INTEGER := 10;
/* declaring variable with
a default value.*/
helloworld VARCHAR DEFAULT 'PostgreSQL rocks !';
BEGIN