PostgreSQL PL/pgSQL Functions: Complete Syntax Guide

PL/pgSQL is PostgreSQL’s procedural language. It adds programming constructs to SQL: variables, conditionals, loops, and error handling. This guide covers the syntax and patterns you’ll use in most functions.

What You’ll Learn

This guide covers:

  • Variable declaration and assignment
  • Data types for variables
  • IF/ELSIF/ELSE conditionals
  • LOOP, WHILE, and FOR loops
  • String concatenation and formatting
  • CASE statements
  • Cursors for row-by-row processing
  • Returning single and multiple values
  • Common patterns and best practices

Function Structure

Every PL/pgSQL function follows this pattern:

CREATE FUNCTION function_name(param1 type, param2 type)
RETURNS return_type AS $$
DECLARE
    -- Variable declarations
    variable_name data_type;
    another_var data_type := initial_value;
BEGIN
    -- Function logic here
    RETURN result;
EXCEPTION
    -- Error handling (optional)
    WHEN exception_type THEN
        -- Handle exception
END;
$$ LANGUAGE plpgsql;

Variables

Declaration

CREATE FUNCTION demo_variables()
RETURNS VARCHAR AS $$
DECLARE
    v_employee_name VARCHAR(100);
    v_salary DECIMAL(10, 2);
    v_hire_date DATE;
    v_years_employed INTEGER;
    v_is_active BOOLEAN DEFAULT TRUE;
    v_count INTEGER := 0;
BEGIN
    -- Variables can be used here
    RETURN 'Variables declared';
END;
$$ LANGUAGE plpgsql;

Declare variables in the DECLARE section:

  • Name convention: v_ prefix for clarity
  • Specify data type (VARCHAR, DECIMAL, DATE, etc.)
  • Optional: := initial_value to set default

Assigning Values

CREATE FUNCTION demo_assignment()
RETURNS VARCHAR AS $$
DECLARE
    v_name VARCHAR;
    v_salary DECIMAL;
BEGIN
    -- Assignment from literal
    v_name := 'James Wilson';
    v_salary := 75000.00;

    -- Assignment from query
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = 1;

    -- Increment/decrement
    v_salary := v_salary * 1.10;  -- Increase by 10%

    RETURN v_name || ' earns $' || v_salary;
END;
$$ LANGUAGE plpgsql;

SELECT demo_assignment();

Result:

demo_assignment
---------------------
James Wilson earns $82500

Use SELECT...INTO to assign query results to variables.

%TYPE for Column-based Variables

Match a variable’s type to a table column.

CREATE FUNCTION get_employee_info(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_first_name employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    SELECT first_name, salary INTO v_first_name, v_salary
    FROM employees
    WHERE emp_id = get_employee_info.emp_id;

    RETURN v_first_name || ' earns ' || v_salary;
END;
$$ LANGUAGE plpgsql;

SELECT get_employee_info(1);

%TYPE automatically uses the column’s data type. If you change the column’s type, the variable updates automatically.

Conditionals

IF/ELSIF/ELSE

CREATE FUNCTION get_salary_category(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_salary DECIMAL;
    v_category VARCHAR;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = get_salary_category.emp_id;

    IF v_salary < 50000 THEN
        v_category := 'Entry Level';
    ELSIF v_salary < 75000 THEN
        v_category := 'Mid Level';
    ELSIF v_salary < 100000 THEN
        v_category := 'Senior';
    ELSE
        v_category := 'Executive';
    END IF;

    RETURN v_category;
END;
$$ LANGUAGE plpgsql;

SELECT emp_id, first_name, salary, get_salary_category(emp_id)
FROM employees
LIMIT 5;

Result:

emp_id | first_name | salary | get_salary_category
-------+------------+--------+---------------------
      1 | James      | 75000  | Mid Level
      2 | Mary       | 78000  | Mid Level
      7 | David      | 105000 | Executive

CASE Statement

Alternative to IF for many conditions.

CREATE FUNCTION get_bonus_tier(years_employed INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_tier VARCHAR;
BEGIN
    CASE
        WHEN years_employed < 1 THEN
            v_tier := 'Tier A (5%)';
        WHEN years_employed < 3 THEN
            v_tier := 'Tier B (7%)';
        WHEN years_employed < 5 THEN
            v_tier := 'Tier C (10%)';
        ELSE
            v_tier := 'Tier D (15%)';
    END CASE;

    RETURN v_tier;
END;
$$ LANGUAGE plpgsql;

SELECT
    emp_id,
    first_name,
    EXTRACT(YEAR FROM AGE(NOW(), hire_date))::INTEGER as years,
    get_bonus_tier(EXTRACT(YEAR FROM AGE(NOW(), hire_date))::INTEGER)
FROM employees
LIMIT 3;

CASE is clearer than multiple ELSIF for simple equality checks.

Loops

LOOP (Infinite Loop)

CREATE FUNCTION generate_numbers(max_num INTEGER)
RETURNS TABLE(num INTEGER) AS $$
DECLARE
    v_counter INTEGER := 1;
BEGIN
    LOOP
        RETURN NEXT v_counter;
        v_counter := v_counter + 1;

        IF v_counter > max_num THEN
            EXIT;  -- Break from loop
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM generate_numbers(5);

Result:

num
-----
  1
  2
  3
  4
  5

Use LOOP with EXIT condition.

WHILE Loop

CREATE FUNCTION factorial(n INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_result INTEGER := 1;
    v_counter INTEGER := 1;
BEGIN
    WHILE v_counter <= n LOOP
        v_result := v_result * v_counter;
        v_counter := v_counter + 1;
    END LOOP;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

SELECT factorial(5) as five_factorial;  -- 5! = 120

Result:

five_factorial
-----------
        120

FOR Loop

CREATE FUNCTION sum_range(start_num INTEGER, end_num INTEGER)
RETURNS INTEGER AS $$
DECLARE
    v_sum INTEGER := 0;
    v_i INTEGER;
BEGIN
    FOR v_i IN start_num..end_num LOOP
        v_sum := v_sum + v_i;
    END LOOP;

    RETURN v_sum;
END;
$$ LANGUAGE plpgsql;

SELECT sum_range(1, 10) as total;  -- 1+2+...+10 = 55

Result:

total
------
   55

FOR Loop Over Query Results

CREATE FUNCTION raise_salaries(dept_id INTEGER, raise_pct DECIMAL)
RETURNS TABLE(emp_id INTEGER, name VARCHAR, old_salary DECIMAL, new_salary DECIMAL) AS $$
DECLARE
    v_emp RECORD;
BEGIN
    FOR v_emp IN
        SELECT emp_id, first_name, last_name, salary
        FROM employees
        WHERE dept_id = raise_salaries.dept_id
    LOOP
        RETURN NEXT (
            v_emp.emp_id,
            v_emp.first_name || ' ' || v_emp.last_name,
            v_emp.salary,
            v_emp.salary * (1 + raise_pct / 100)
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM raise_salaries(2, 10);

Result:

emp_id | name              | old_salary | new_salary
-------+-------------------+------------+-----------
      7 | David Miller      | 105000     | 115500
      8 | Jennifer Martinez | 102000     | 112200

String Operations

Concatenation with ||

CREATE FUNCTION build_email(first_name VARCHAR, last_name VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    v_email VARCHAR;
BEGIN
    v_email := LOWER(first_name) || '.' || LOWER(last_name) || '@company.com';
    RETURN v_email;
END;
$$ LANGUAGE plpgsql;

SELECT build_email('James', 'Wilson') as email;

Result:

email
---------------------------
[email protected]

String Functions

CREATE FUNCTION format_employee_info(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_first VARCHAR;
    v_last VARCHAR;
    v_output VARCHAR;
BEGIN
    SELECT first_name, last_name INTO v_first, v_last
    FROM employees
    WHERE emp_id = format_employee_info.emp_id;

    -- String functions
    v_output := UPPER(v_first) || ' ' || UPPER(v_last);  -- Uppercase
    v_output := INITCAP(v_first) || ' ' || INITCAP(v_last);  -- Title case
    v_output := SUBSTR(v_first, 1, 3) || ' ' || v_last;  -- Substring

    RETURN v_output;
END;
$$ LANGUAGE plpgsql;

SELECT format_employee_info(1);

Available string functions: UPPER, LOWER, INITCAP, SUBSTR, LENGTH, TRIM, LTRIM, RTRIM, REPLACE, SPLIT_PART, CONCAT.

Working with Records

RECORD Type

Store entire row data.

CREATE FUNCTION get_full_employee(emp_id INTEGER)
RETURNS VARCHAR AS $$
DECLARE
    v_emp RECORD;
BEGIN
    SELECT * INTO v_emp
    FROM employees
    WHERE emp_id = get_full_employee.emp_id;

    RETURN v_emp.first_name || ' ' || v_emp.last_name ||
           ' (ID: ' || v_emp.emp_id || ', Salary: $' || v_emp.salary || ')';
END;
$$ LANGUAGE plpgsql;

SELECT get_full_employee(1);

Result:

get_full_employee
------------------------------------
James Wilson (ID: 1, Salary: $75000)

Returning Values

RETURN for Single Values

CREATE FUNCTION calculate_annual_bonus(emp_id INTEGER, bonus_pct DECIMAL)
RETURNS DECIMAL AS $$
DECLARE
    v_salary DECIMAL;
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = calculate_annual_bonus.emp_id;

    RETURN v_salary * (bonus_pct / 100);
END;
$$ LANGUAGE plpgsql;

SELECT emp_id, first_name, calculate_annual_bonus(emp_id, 10) as bonus
FROM employees LIMIT 3;

RETURN NEXT for Multiple Rows

CREATE FUNCTION get_all_employees()
RETURNS TABLE(id INTEGER, name VARCHAR, salary DECIMAL) AS $$
DECLARE
    v_emp RECORD;
BEGIN
    FOR v_emp IN SELECT * FROM employees LOOP
        RETURN NEXT (v_emp.emp_id, v_emp.first_name || ' ' || v_emp.last_name, v_emp.salary);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM get_all_employees() LIMIT 3;

RETURN NEXT adds a row to the result set. Final RETURN ends the function.

Cursor Loops

Explicit Cursors

Iterate through query results with fine control.

CREATE FUNCTION sum_department_salary(dept_id INTEGER)
RETURNS DECIMAL AS $$
DECLARE
    v_cursor CURSOR FOR
        SELECT salary FROM employees
        WHERE dept_id = sum_department_salary.dept_id;
    v_salary DECIMAL;
    v_total DECIMAL := 0;
BEGIN
    OPEN v_cursor;

    LOOP
        FETCH v_cursor INTO v_salary;
        EXIT WHEN NOT FOUND;  -- Exit when no more rows
        v_total := v_total + v_salary;
    END LOOP;

    CLOSE v_cursor;
    RETURN v_total;
END;
$$ LANGUAGE plpgsql;

SELECT sum_department_salary(2) as engineering_payroll;

Result:

engineering_payroll
-----------
      781000

Common Patterns

Updating with Array Loop

CREATE PROCEDURE update_salary_list(emp_ids INTEGER[])
LANGUAGE plpgsql
AS $$
DECLARE
    v_emp_id INTEGER;
BEGIN
    FOREACH v_emp_id IN ARRAY emp_ids LOOP
        UPDATE employees
        SET salary = salary * 1.05
        WHERE emp_id = v_emp_id;
    END LOOP;
END;
$$;

CALL update_salary_list(ARRAY[1, 2, 3]);

Input Validation

CREATE FUNCTION safe_salary_update(emp_id INTEGER, new_salary DECIMAL)
RETURNS VARCHAR AS $$
BEGIN
    IF emp_id IS NULL THEN
        RAISE EXCEPTION 'Employee ID cannot be NULL';
    END IF;

    IF new_salary <= 0 THEN
        RAISE EXCEPTION 'Salary must be positive';
    END IF;

    IF new_salary > 500000 THEN
        RAISE WARNING 'Salary exceeds company maximum';
    END IF;

    UPDATE employees SET salary = new_salary WHERE emp_id = safe_salary_update.emp_id;
    RETURN 'Salary updated successfully';
END;
$$ LANGUAGE plpgsql;

Common Errors & Solutions

Error: “variable “$1” does not exist”

Problem:

CREATE FUNCTION bad_concat(first VARCHAR, last VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN first || ' ' || last;  -- Wrong: $1 refers to parameter
END;
$$ LANGUAGE plpgsql;

Parameters need qualified reference.

Solution:

Use bad_concat.first or assign to variable:

CREATE FUNCTION good_concat(first VARCHAR, last VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN good_concat.first || ' ' || good_concat.last;
END;
$$ LANGUAGE plpgsql;

-- Or use variables
CREATE FUNCTION good_concat2(first VARCHAR, last VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    v_first VARCHAR := first;
    v_last VARCHAR := last;
BEGIN
    RETURN v_first || ' ' || v_last;
END;
$$ LANGUAGE plpgsql;

Error: “INTO used with a query that returns no rows”

Problem:

SELECT salary INTO v_salary
FROM employees
WHERE emp_id = 9999;  -- Employee doesn't exist

Solution:

Check for NULL or use exception handling:

SELECT salary INTO v_salary
FROM employees
WHERE emp_id = 9999;

IF v_salary IS NULL THEN
    v_salary := 0;
END IF;

-- Or use exception
BEGIN
    SELECT salary INTO STRICT v_salary
    FROM employees WHERE emp_id = 9999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        v_salary := 0;
END;

Performance Tips

1. Use Immutable Declaration

CREATE FUNCTION double_it(n INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN n * 2;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Allows PostgreSQL to cache results.

2. Avoid Loops When Possible

-- Slow: Loop over rows
CREATE FUNCTION slow_raise_all() AS $$
FOR emp_rec IN SELECT * FROM employees LOOP
    UPDATE employees SET salary = salary * 1.05 WHERE emp_id = emp_rec.emp_id;
END LOOP;

-- Fast: Bulk update
UPDATE employees SET salary = salary * 1.05;

3. Use Set-Based Operations

-- Slow: Loop with individual updates
FOR v_rec IN SELECT * FROM employees WHERE dept_id = 2 LOOP
    UPDATE salaries SET salary = salary * 1.1 WHERE emp_id = v_rec.emp_id;
END LOOP;

-- Fast: Single update
UPDATE salaries SET salary = salary * 1.1
WHERE emp_id IN (SELECT emp_id FROM employees WHERE dept_id = 2);

FAQ

Q: What’s the difference between v_variable and $1 parameter reference? A: Variables are declared in DECLARE. Parameters are accessed with function_name.param_name or assigned to variables first.

Q: Should I use LOOP or WHILE? A: WHILE for condition-based repetition. LOOP for exit-based. FOR for known ranges.

Q: How do I debug PL/pgSQL? A: Use RAISE NOTICE 'message: ' || variable; to print debug output. Check logs for output.

Q: Can I call other functions from PL/pgSQL? A: Yes, call them like you would in SQL: SELECT my_function(param) INTO v_result;

Q: What’s the %TYPE operator used for? A: Declare variables with the same type as a table column. Automatically updates if column type changes.

Continue learning PL/pgSQL:

PL/pgSQL is powerful for encapsulating business logic. Master these syntax patterns and you can write maintainable database functions.