7.6.3. DECLARE VARIABLE
Used for
Declaring a local variable
Available in
PSQL
Syntax
DECLARE [VARIABLE] varname
{<datatype> | domain | TYPE OF {domain | COLUMN rel.col}
[NOT NULL] [CHARACTER SET charset] [COLLATE collation]
[{DEFAULT | = } <initvalue>];
<datatype> ::=
{SMALLINT | INTEGER | BIGINT}
| {FLOAT | DOUBLE PRECISION}
| {DATE | TIME | TIMESTAMP}
| {DECIMAL | NUMERIC} [(precision [, scale])]
| {CHAR | CHARACTER [VARYING] | VARCHAR} [(size)]
[CHARACTER SET charset]
| {NCHAR | NATIONAL {CHARACTER | CHAR}} [VARYING]
[(size)]
| BLOB [SUB_TYPE {subtype_num | subtype_name}]
[SEGMENT SIZE seglen] [CHARACTER SET charset]
| BLOB [(seglen [, subtype_num])]
<initvalue> ::= <literal> | <context_var>
Argument | Description |
---|---|
varname | Name of the local variable |
datatype | An SQL data type |
domain | The name of an existing domain in this database |
rel.col | Relation name (table or view) in this database and the name of a column in that relation |
precision | Precision. From 1 to 18 |
scale | Scale. From 0 to 18, it must be less than or equal to precision |
size | The maximum size of a string in characters |
subtype_num |
|
subtype_name |
|
seglen | Segment size, not greater than 65,535 |
initvalue | Initial value for this variable |
literal | Literal of a type compatible with the type of the local variable |
context_var | Any context variable whose type is compatible with the type of the local variable |
charset | Character set |
collation | Collation sequence |
The statement DECLARE [VARIABLE]
is used for declaring a local variable. The keyword VARIABLE
can be omitted. One DECLARE [VARIABLE]
statement is required for each local variable. Any number of DECLARE [VARIABLE]
statements can be included and in any order. The name of a local variable must be unique among the names of local variables and input and output parameters declared for the module.
Data Type for Variables
A local variable can be of any SQL type other than an array.
A domain name can be specified as the type and the variable will inherit all of its attributes.
If the
TYPE OF *domain*
clause is used instead, the variable will inherit only the domain’s data type, and, if applicable, its character set and collation attributes. Any default value or constraints such asNOT NULL
orCHECK
constraints are not inherited.If the
TYPE OF COLUMN *relation*.*column*>
option is used to “borrow” from a column in a table or view, the variable will inherit only the column’s data type, and, if applicable, its character set and collation attributes. Any other attributes are ignored.
NOT NULL
Constraint
The variable can be constrained NOT NULL
if required. If a domain has been specified as the data type and already carries the NOT NULL
constraint, it will not be necessary. With the other forms, including use of a domain that is nullable, the NOT NULL
attribute should be included if needed.
CHARACTER SET
and COLLATE
clauses
Unless specified, the character set and collation sequence of a string variable will be the database defaults. A CHARACTER SET
clause can be included, if required, to handle string data that is going to be in a different character set. A valid collation sequence (COLLATE
clause) can also be included, with or without the character set clause.
Initializing a Variable
Local variables are NULL
when execution of the module begins. They can be initialized so that a starting or default value is available when they are first referenced. The DEFAULT <initvalue>
form can be used, or just the assignment operator, ‘=
’: = <initvalue>
. The value can be any type-compatible literal or context variable.
Be sure to use this clause for any variables that are constrained to be |
Examples of various ways to declare local variables
CREATE OR ALTER PROCEDURE SOME_PROC
AS
-- Declaring a variable of the INT type
DECLARE I INT;
-- Declaring a variable of the INT type that does not allow NULL
DECLARE VARIABLE J INT NOT NULL;
-- Declaring a variable of the INT type with the default value of 0
DECLARE VARIABLE K INT DEFAULT 0;
-- Declaring a variable of the INT type with the default value of 1
DECLARE VARIABLE L INT = 1;
-- Declaring a variable based on the COUNTRYNAME domain
DECLARE FARM_COUNTRY COUNTRYNAME;
-- Declaring a variable of the type equal to the COUNTRYNAME domain
DECLARE FROM_COUNTRY TYPE OF COUNTRYNAME;
-- Declaring a variable with the type of the CAPITAL column in the COUNTRY table
DECLARE CAPITAL TYPE OF COLUMN COUNTRY.CAPITAL;
BEGIN
/* PSQL statements */
END
See also
Data Types and Subtypes, Custom Data Types — Domains, CREATE DOMAIN