Procedural Language (PL)¶

HeliosDB-Lite 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