Procedural Language (PL)

Procedural Language (PL)

HeliosDB Nano includes a multi-dialect procedural language engine supporting PL/pgSQL, T-SQL, PL/SQL, and DB2 SQL PL syntax.

Overview

The procedural language module enables:

• Stored Procedures: Encapsulate complex business logic
• User-Defined Functions: Create reusable computations
• Control Flow: IF/ELSE, LOOP, WHILE, FOR statements
• Variables: Declare, assign, and manipulate local variables
• Exception Handling: EXCEPTION blocks for error handling
• Cursors: Iterate over query results

Supported Dialects

Dialect	Description	Primary Use
PL/pgSQL	PostgreSQL procedural language	Default, PostgreSQL compatibility
T-SQL	Microsoft SQL Server	SQL Server migrations
PL/SQL	Oracle Database	Oracle migrations
DB2 SQL PL	IBM DB2	DB2 migrations
Auto	Auto-detect dialect	Mixed environments

Basic Syntax

Anonymous Blocks

DO $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM users;
    RAISE NOTICE 'Total users: %', v_count;
END;
$$;

CREATE FUNCTION

CREATE OR REPLACE FUNCTION get_user_count()
RETURNS INTEGER AS $$
DECLARE
    v_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_count FROM users;
    RETURN v_count;
END;
$$ LANGUAGE plpgsql;

CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE update_user_status(
    p_user_id INTEGER,
    p_status VARCHAR
)
AS $$
BEGIN
    UPDATE users SET status = p_status WHERE id = p_user_id;
END;
$$ LANGUAGE plpgsql;

Variables

Declaration

DECLARE
    v_name VARCHAR(100);           -- Simple variable
    v_count INTEGER := 0;          -- With default value
    v_active CONSTANT BOOLEAN := TRUE;  -- Constant
    v_user users%ROWTYPE;          -- Row type
    v_id users.id%TYPE;            -- Column type

Assignment

-- Direct assignment
v_count := 42;

-- SELECT INTO
SELECT name INTO v_name FROM users WHERE id = 1;

-- Multiple values
SELECT name, email INTO v_name, v_email FROM users WHERE id = 1;

Control Flow

IF Statement

IF v_count > 100 THEN
    RAISE NOTICE 'High count';
ELSIF v_count > 50 THEN
    RAISE NOTICE 'Medium count';
ELSE
    RAISE NOTICE 'Low count';
END IF;

CASE Statement

CASE v_status
    WHEN 'active' THEN
        RETURN 1;
    WHEN 'inactive' THEN
        RETURN 0;
    ELSE
        RETURN -1;
END CASE;

LOOP

-- Basic loop
LOOP
    v_count := v_count + 1;
    EXIT WHEN v_count >= 10;
END LOOP;

-- WHILE loop
WHILE v_count < 10 LOOP
    v_count := v_count + 1;
END LOOP;

-- FOR loop (numeric range)
FOR i IN 1..10 LOOP
    RAISE NOTICE 'Iteration: %', i;
END LOOP;

-- FOR loop (query result)
FOR rec IN SELECT * FROM users LOOP
    RAISE NOTICE 'User: %', rec.name;
END LOOP;

Exception Handling

BEGIN
    -- Code that might fail
    INSERT INTO users (id, name) VALUES (1, 'Alice');
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'User already exists';
    WHEN OTHERS THEN
        RAISE NOTICE 'Unexpected error: %', SQLERRM;
END;

Common Exception Types

Exception	Description
unique_violation	Duplicate key
foreign_key_violation	FK constraint failed
null_value_not_allowed	NULL in NOT NULL column
check_violation	CHECK constraint failed
division_by_zero	Division by zero
OTHERS	Catch-all handler

Cursors

DECLARE
    user_cursor CURSOR FOR SELECT * FROM users;
    user_record users%ROWTYPE;
BEGIN
    OPEN user_cursor;
    LOOP
        FETCH user_cursor INTO user_record;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'User: %', user_record.name;
    END LOOP;
    CLOSE user_cursor;
END;

Dialect-Specific Examples

T-SQL Style

-- T-SQL uses @ for variables
DECLARE @v_count INT;
SET @v_count = 0;

WHILE @v_count < 10
BEGIN
    SET @v_count = @v_count + 1;
    PRINT @v_count;
END;

PL/SQL Style

-- PL/SQL uses := for assignment
DECLARE
    v_count NUMBER := 0;
BEGIN
    FOR i IN 1..10 LOOP
        v_count := v_count + 1;
        DBMS_OUTPUT.PUT_LINE(v_count);
    END LOOP;
END;

Integration with Triggers

Procedural language functions are commonly used as trigger handlers:

-- Create trigger function
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, action, changed_at)
    VALUES (TG_TABLE_NAME, TG_OP, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Attach to trigger
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_changes();

Built-in Functions

Utility Functions

Function	Description
RAISE NOTICE	Output debug message
RAISE EXCEPTION	Throw error
FOUND	Boolean - last query returned rows
ROW_COUNT	Rows affected by last statement
NOW()	Current timestamp

Trigger Context Variables

Variable	Description
TG_OP	Operation (INSERT, UPDATE, DELETE)
TG_TABLE_NAME	Name of trigger table
OLD	Row before modification
NEW	Row after modification
TG_WHEN	BEFORE or AFTER

Performance Considerations

• PL functions are compiled and cached
• Avoid excessive dynamic SQL
• Use bulk operations where possible
• Consider using IMMUTABLE/STABLE/VOLATILE appropriately

See Also

• Triggers
• SQL Reference
• Feature Index