PL/PGSQL Variable and Datatype Declaration

PL/pgSQL Variables:

A PL/pgSQL variable is a meaningful name for a memory location. A variable holds a value that can be changed through the block or function. A variable is always associated with a particular data type.

variable_name data_type [:= expression];

Data type:

INTEGER, NUMERIC, VARCHAR and CHAR.

Input:

DO $$
DECLARE
a INTEGER := 1;
first_name VARCHAR(50) := 'muthu';
last_name VARCHAR(50) := 'siva';
payment NUMERIC(11,2) := 10.5;
BEGIN
RAISE NOTICE '% % % has been paid % USD', a, first_name, last_name, payment;
END $$;

Copying data types:

Syntax:

variable_name table_name.column_name%TYPE;

Example:

city_name city.name%TYPE := 'San Francisco';

PL/pgSQL Constants:

Unlike variables, the values of constants cannot be changed once they are initialized. The following are reasons to use constants.

First, the constants make the code more readable e.g., imagine that we have a formula as follows:

selling_price = net_price + net_price * 0.1;

Syntax:

constant_name CONSTANT data_type := expression;

Example:

DO $$
DECLARE
VAT CONSTANT NUMERIC := 0.1;
net_price NUMERIC := 20.5;
BEGIN
RAISE NOTICE 'The selling price is %', net_price * ( 1 + VAT );
END $$;