How to Use DECLARE in PL/pgSQL: Syntax and Examples

In PL/pgSQL, use the DECLARE section immediately after the BEGIN keyword in a block to define variables, cursors, or constants before the executable code. This section allows you to name and set types for these elements, which you can then use inside the block's logic.

Syntax

The DECLARE section appears immediately after the BEGIN keyword in a PL/pgSQL block or function. Here you list variables or cursors with their names and data types. Each declaration ends with a semicolon.

Basic parts:

• Variable name: The identifier you will use in code.
• Data type: The type of data the variable holds (e.g., integer, text).
• Optional initialization: You can assign an initial value using :=.

sql

DECLARE
    variable_name data_type [:= initial_value];
    cursor_name CURSOR FOR query;
    constant_name CONSTANT data_type := value;

Example

This example shows a PL/pgSQL function that declares variables to calculate the sum of two numbers and returns the result.

sql

CREATE OR REPLACE FUNCTION add_two_numbers(a integer, b integer) RETURNS integer AS $$
DECLARE
    sum_result integer := 0;
BEGIN
    sum_result := a + b;
    RETURN sum_result;
END;
$$ LANGUAGE plpgsql;

Output

 add_two_numbers
----------------
             7
(1 row)

Common Pitfalls

Common mistakes when using DECLARE in PL/pgSQL include:

• Placing DECLARE outside the block or before BEGIN (it must be immediately after BEGIN or in the function header).
• Not ending declarations with semicolons.
• Using variables without declaring them first.
• Trying to assign values in the DECLARE section without :=.

sql

/* Wrong: DECLARE before BEGIN */
-- DECLARE
--     x integer;
-- BEGIN
--     x := 5;
-- END;

/* Right: DECLARE after BEGIN */
DO $$
DECLARE
    x integer := 5;
BEGIN
    RAISE NOTICE 'Value of x: %', x;
END;
$$ LANGUAGE plpgsql;

Output

NOTICE:  Value of x: 5

Quick Reference

Remember these tips when using DECLARE in PL/pgSQL:

• Always place DECLARE after BEGIN in anonymous blocks or inside function bodies.
• End each declaration with a semicolon.
• Initialize variables with := if needed.
• Use CONSTANT keyword for fixed values.
• Cursors are declared with CURSOR FOR followed by a query.

Key Takeaways

Use the DECLARE section immediately after BEGIN to define variables and cursors in PL/pgSQL.

Always end each declaration with a semicolon and initialize variables using := if needed.

Variables must be declared before use; otherwise, the code will fail to compile.

Place DECLARE inside the block or function, not outside it.

Use CONSTANT for fixed values and CURSOR FOR to declare cursors.