Variables and Data Types

Variables store values during the execution of a PL/pgSQL block. They must be declared in the DECLARE section before use.

Declaring Variables

variable_name [ CONSTANT ] data_type [ NOT NULL ] [ { DEFAULT | := | = } expression ];

Examples:

DECLARE
  v_name     TEXT;
  v_age      INTEGER        := 0;
  v_active   BOOLEAN        := TRUE;
  v_price    NUMERIC(10, 2) DEFAULT 0.00;
  v_today    DATE           := CURRENT_DATE;
  tax_rate   CONSTANT NUMERIC := 0.20;

Common Data Types

Text

Type	Notes
TEXT	Unlimited length; the idiomatic PostgreSQL string type
VARCHAR(n)	Variable-length, maximum n characters
CHAR(n)	Fixed-length, padded with spaces

Numeric

Type	Notes
INTEGER / INT	4-byte whole number
BIGINT	8-byte whole number
NUMERIC(p,s)	Arbitrary precision decimal
REAL / DOUBLE PRECISION	Floating-point

Date and Time

Type	Notes
DATE	Calendar date only
TIMESTAMP	Date + time (no timezone)
TIMESTAMPTZ	Date + time with timezone
INTERVAL	A span of time

Other

Type	Notes
BOOLEAN	TRUE, FALSE, or NULL
UUID	Universally unique identifier
JSON / JSONB	JSON data

%TYPE

Borrow a variable's type from a table column — adapts automatically if the schema changes:

DECLARE
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = 1;
  RAISE NOTICE 'Salary: %', v_salary;
END;

%ROWTYPE

Declare a record variable with the same structure as a full table row:

DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = 1;
  RAISE NOTICE '% earns %', v_emp.first_name, v_emp.salary;
END;

RECORD Type

A RECORD variable can hold a row from any query — its structure is set when a row is assigned to it:

DECLARE
  rec RECORD;
BEGIN
  SELECT id, first_name INTO rec FROM employees WHERE id = 1;
  RAISE NOTICE 'ID: %, Name: %', rec.id, rec.first_name;
END;

Constants

tax_rate CONSTANT NUMERIC := 0.20;

Attempting to assign a new value to a constant causes a compile error.

NOT NULL

v_count INTEGER NOT NULL := 0;