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 $$;