/Docs
Docs›Guides›Query›Functions›Plsql Udf

PL/pgSQL UDFs

Write procedural database functions using PL/pgSQL for control flow, calculations, and data transformations without external dependencies.

Overview

PL/pgSQL is PostgreSQL’s built-in procedural language. Unlike PL/Python, it doesn’t require any external runtime—it’s always available and runs directly in the database engine.

Why use PL/pgSQL?

  • No setup required: Works out of the box, no extensions to install
  • Familiar SQL syntax: If you know SQL, you’re halfway there
  • Control flow: Variables, loops, conditionals, exceptions
  • Fast for simple logic: No cross-process overhead like with PL/Python

The current version does not support table access (SELECT, INSERT, UPDATE, DELETE) within PL/pgSQL functions. Use it for computational logic, validation, and data transformation only.

Basic Syntax

CREATE OR REPLACE FUNCTION function_name(param1 TYPE, param2 TYPE)
RETURNS return_type
AS $$
DECLARE
    -- Variable declarations
    my_var INTEGER := 0;
BEGIN
    -- Your logic here
    RETURN result;
END;
$$ LANGUAGE plpgsql;

Variables and Types

Declare variables in the DECLARE block:

DECLARE
    -- Numbers
    counter INTEGER := 0;
    price NUMERIC(10, 2);

    -- Strings
    name VARCHAR(50) := 'guest';
    message TEXT;

    -- Boolean
    is_valid BOOLEAN DEFAULT false;

    -- Dates
    created_at TIMESTAMP := NOW();

    -- Arrays
    scores INTEGER[] := ARRAY[90, 85, 78, 92];
    tags TEXT[] := ARRAY['urgent', 'review'];

    -- JSON
    config JSONB;

Control Flow

If/Else

IF age >= 21 THEN
    status := 'adult';
ELSIF age >= 13 THEN
    status := 'teenager';
ELSE
    status := 'child';
END IF;

Loops

FOR loop with range:

FOR i IN 1..10 LOOP
    total := total + i;
END LOOP;

FOR loop with array:

FOREACH item IN ARRAY my_array LOOP
    -- process item
END LOOP;

WHILE loop:

WHILE counter < 100 LOOP
    counter := counter + 1;
END LOOP;

CASE Expression

result := CASE grade
    WHEN 'A' THEN 'Excellent'
    WHEN 'B' THEN 'Good'
    WHEN 'C' THEN 'Average'
    ELSE 'Needs improvement'
END;

Practical Examples

Password Strength Checker

CREATE OR REPLACE FUNCTION check_password_strength(password TEXT)
RETURNS TEXT
AS $$
DECLARE
    score INTEGER := 0;
BEGIN
    -- Length checks
    IF LENGTH(password) >= 8 THEN score := score + 1; END IF;
    IF LENGTH(password) >= 12 THEN score := score + 1; END IF;

    -- Character type checks
    IF password ~ '[A-Z]' THEN score := score + 1; END IF;
    IF password ~ '[a-z]' THEN score := score + 1; END IF;
    IF password ~ '[0-9]' THEN score := score + 1; END IF;
    IF password ~ '[^A-Za-z0-9]' THEN score := score + 1; END IF;

    RETURN CASE score
        WHEN 0 THEN 'very weak'
        WHEN 1 THEN 'weak'
        WHEN 2 THEN 'fair'
        WHEN 3 THEN 'fair'
        WHEN 4 THEN 'good'
        WHEN 5 THEN 'strong'
        ELSE 'very strong'
    END;
END;
$$ LANGUAGE plpgsql;

SELECT check_password_strength('MyP@ssw0rd!');  -- 'very strong'

Phone Number Formatter

CREATE OR REPLACE FUNCTION format_phone(raw TEXT)
RETURNS TEXT
AS $$
DECLARE
    digits TEXT;
BEGIN
    -- Strip everything except digits
    digits := REGEXP_REPLACE(raw, '[^0-9]', '', 'g');

    -- Format as XXX-XXXX-XXXX if 11 digits
    IF LENGTH(digits) = 11 THEN
        RETURN SUBSTRING(digits, 1, 3) || '-' ||
               SUBSTRING(digits, 4, 4) || '-' ||
               SUBSTRING(digits, 8, 4);
    END IF;

    -- Return as-is if different length
    RETURN digits;
END;
$$ LANGUAGE plpgsql;

SELECT format_phone('138 1234 5678');  -- '138-1234-5678'

Business Days Calculator

CREATE OR REPLACE FUNCTION count_business_days(start_date DATE, end_date DATE)
RETURNS INTEGER
AS $$
DECLARE
    current_date DATE;
    count INTEGER := 0;
BEGIN
    current_date := start_date;

    WHILE current_date <= end_date LOOP
        -- Skip weekends (0 = Sunday, 6 = Saturday)
        IF EXTRACT(DOW FROM current_date) NOT IN (0, 6) THEN
            count := count + 1;
        END IF;
        current_date := current_date + 1;
    END LOOP;

    RETURN count;
END;
$$ LANGUAGE plpgsql;

SELECT count_business_days('2024-01-01', '2024-01-31');

Input Validation

CREATE OR REPLACE FUNCTION validate_user(
    username TEXT,
    email TEXT,
    age INTEGER
)
RETURNS JSONB
AS $$
DECLARE
    errors TEXT[] := '{}';
BEGIN
    -- Username checks
    IF LENGTH(username) < 3 THEN
        errors := array_append(errors, 'Username must be at least 3 characters');
    END IF;

    -- Email format check
    IF email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        errors := array_append(errors, 'Invalid email format');
    END IF;

    -- Age check
    IF age < 13 OR age > 120 THEN
        errors := array_append(errors, 'Age must be between 13 and 120');
    END IF;

    IF array_length(errors, 1) > 0 THEN
        RETURN jsonb_build_object('valid', false, 'errors', errors);
    END IF;

    RETURN jsonb_build_object('valid', true);
END;
$$ LANGUAGE plpgsql;

SELECT validate_user('jo', 'not-an-email', 150);
-- {"valid": false, "errors": ["Username must be at least 3 characters", "Invalid email format", "Age must be between 13 and 120"]}

Array Statistics

CREATE OR REPLACE FUNCTION array_stats(numbers NUMERIC[])
RETURNS JSONB
AS $$
DECLARE
    total NUMERIC := 0;
    min_val NUMERIC;
    max_val NUMERIC;
    i INTEGER;
BEGIN
    IF array_length(numbers, 1) IS NULL THEN
        RETURN '{"error": "empty array"}'::JSONB;
    END IF;

    min_val := numbers[1];
    max_val := numbers[1];

    FOR i IN 1..array_length(numbers, 1) LOOP
        total := total + numbers[i];
        IF numbers[i] < min_val THEN min_val := numbers[i]; END IF;
        IF numbers[i] > max_val THEN max_val := numbers[i]; END IF;
    END LOOP;

    RETURN jsonb_build_object(
        'count', array_length(numbers, 1),
        'sum', total,
        'avg', ROUND(total / array_length(numbers, 1), 2),
        'min', min_val,
        'max', max_val,
        'range', max_val - min_val
    );
END;
$$ LANGUAGE plpgsql;

SELECT array_stats(ARRAY[10, 20, 30, 40, 50]);

Loan Payment Calculator

CREATE OR REPLACE FUNCTION calculate_loan(
    principal NUMERIC,
    annual_rate NUMERIC,
    years INTEGER
)
RETURNS JSONB
AS $$
DECLARE
    monthly_rate NUMERIC;
    num_payments INTEGER;
    monthly_payment NUMERIC;
    total_payment NUMERIC;
BEGIN
    IF principal <= 0 OR annual_rate < 0 OR years <= 0 THEN
        RETURN '{"error": "Invalid parameters"}'::JSONB;
    END IF;

    monthly_rate := annual_rate / 100 / 12;
    num_payments := years * 12;

    IF monthly_rate = 0 THEN
        monthly_payment := principal / num_payments;
    ELSE
        monthly_payment := principal *
            (monthly_rate * POWER(1 + monthly_rate, num_payments)) /
            (POWER(1 + monthly_rate, num_payments) - 1);
    END IF;

    total_payment := monthly_payment * num_payments;

    RETURN jsonb_build_object(
        'monthly_payment', ROUND(monthly_payment, 2),
        'total_payment', ROUND(total_payment, 2),
        'total_interest', ROUND(total_payment - principal, 2)
    );
END;
$$ LANGUAGE plpgsql;

SELECT calculate_loan(200000, 4.5, 30);
-- {"monthly_payment": 1013.37, "total_payment": 364813.42, "total_interest": 164813.42}

Config Parser

Parse key-value configuration strings into JSON:

CREATE OR REPLACE FUNCTION parse_config(config_text TEXT)
RETURNS JSONB
AS $$
DECLARE
    result JSONB := '{}';
    lines TEXT[];
    line TEXT;
    parts TEXT[];
BEGIN
    lines := STRING_TO_ARRAY(config_text, E'\n');

    FOREACH line IN ARRAY lines LOOP
        -- Skip empty lines and comments
        IF line = '' OR line LIKE '#%' THEN
            CONTINUE;
        END IF;

        -- Parse key=value
        IF POSITION('=' IN line) > 0 THEN
            parts := STRING_TO_ARRAY(line, '=');
            result := result || jsonb_build_object(
                TRIM(parts[1]),
                TRIM(parts[2])
            );
        END IF;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT parse_config('
host=localhost
port=5432
# This is a comment
database=myapp
');
-- {"host": "localhost", "port": "5432", "database": "myapp"}

Random String Generator

CREATE OR REPLACE FUNCTION random_string(
    length INTEGER DEFAULT 10,
    charset TEXT DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
)
RETURNS TEXT
AS $$
DECLARE
    result TEXT := '';
    i INTEGER;
BEGIN
    FOR i IN 1..length LOOP
        result := result || SUBSTRING(charset, FLOOR(RANDOM() * LENGTH(charset) + 1)::INTEGER, 1);
    END LOOP;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

SELECT random_string(8);           -- 'kX7mP2nQ'
SELECT random_string(6, '0123456789');  -- '847291'

Error Handling

Use RAISE to signal errors and EXCEPTION blocks to catch them:

CREATE OR REPLACE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION 'Cannot divide by zero';
    END IF;

    RETURN a / b;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Severity levels for RAISE:

  • DEBUG — For development logging
  • LOG — Server log only
  • NOTICE — Client notification (default)
  • WARNING — Warning message
  • EXCEPTION — Abort the current transaction

Performance Tips

Mark Functions as IMMUTABLE When Possible

If a function always returns the same output for the same input, mark it IMMUTABLE. This lets the planner optimize queries:

CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    RETURN amount * 0.08;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

Volatility options:

  • VOLATILE — Default. Can return different results on each call.
  • STABLE — Returns same result within a single query.
  • IMMUTABLE — Always returns same result for same inputs. Never accesses the database.

Use STRICT for NULL Handling

If your function should return NULL when any input is NULL, use STRICT to avoid writing null checks:

CREATE OR REPLACE FUNCTION multiply(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
BEGIN
    RETURN a * b;
END;
$$ LANGUAGE plpgsql STRICT;

SELECT multiply(5, NULL);  -- Returns NULL automatically