Developing UDFs with PL/pgSQL

DECLARE
    -- Basic types
    counter INTEGER := 0;
    total_amount NUMERIC(10, 2);
    user_name VARCHAR(50) := 'guest';
    is_valid BOOLEAN DEFAULT false;
    current_time TIMESTAMP := NOW();
 
    -- Array types
    scores INTEGER[] := ARRAY[90, 85, 78, 92];
    names TEXT[] := ARRAY['Alice', 'Bob', 'Charlie'];
 
    -- Record types
    user_record RECORD;
    complex_data JSONB;

-- Multi-condition composite judgment
IF age >= 18 AND has_id = true AND (membership_level > 1 OR is_vip = true) THEN
    access_granted := true;
    welcome_message := 'Distinguished member, welcome!';
ELSIF age >= 18 AND has_id = true THEN
    access_granted := true;
    welcome_message := 'Welcome!';
ELSE
    access_granted := false;
    welcome_message := 'Sorry, you do not meet entry requirements';
END IF;

-- Traverse array and process
DECLARE
    numbers INTEGER[] := ARRAY[1, 2, 3, 4, 5];
    squared_numbers INTEGER[];
    total INTEGER := 0;
BEGIN
    FOR i IN 1..array_length(numbers, 1) LOOP
        squared_numbers[i] := numbers[i] * numbers[i];
        total := total + numbers[i];
    END LOOP;
 
    -- Calculate average
    avg_value := total / array_length(numbers, 1);
END;

-- Generate multiplication table
FOR i IN 1..9 LOOP
    FOR j IN 1..9 LOOP
        result := i * j;
        -- Can store to array or process directly
    END LOOP;
END LOOP;

CREATE OR REPLACE FUNCTION calculate_compound_interest(
    principal NUMERIC,
    rate NUMERIC,
    years INTEGER
) RETURNS NUMERIC AS $$
DECLARE
    amount NUMERIC;
BEGIN
    amount := principal * POWER(1 + rate/100, years);
    RETURN ROUND(amount, 2);
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION format_phone_number(raw_phone TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Remove all non-digit characters
    raw_phone := REGEXP_REPLACE(raw_phone, '[^0-9]', '', 'g');
 
    -- Format to standard phone number format
    IF LENGTH(raw_phone) = 11 THEN
        RETURN SUBSTRING(raw_phone FROM 1 FOR 3) || '-' ||
               SUBSTRING(raw_phone FROM 4 FOR 4) || '-' ||
               SUBSTRING(raw_phone FROM 8 FOR 4);
    ELSE
        RETURN raw_phone;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_business_days(
    start_date DATE,
    end_date DATE
) RETURNS INTEGER AS $$
DECLARE
    curr_date DATE;
    business_days INTEGER := 0;
BEGIN
    curr_date := start_date;
 
    WHILE curr_date <= end_date LOOP
        -- Exclude weekends (0=Sunday, 6=Saturday)
        IF EXTRACT(DOW FROM curr_date) NOT IN (0, 6) THEN
            business_days := business_days + 1;
        END IF;
        curr_date := curr_date + 1;
    END LOOP;
 
    RETURN business_days;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION validate_user_input(
    username TEXT,
    email TEXT,
    age INTEGER
) RETURNS JSONB AS $$
DECLARE
    validation_result JSONB := '{"valid": true, "errors": []}';
    error_message TEXT;
BEGIN
    -- Username validation
    IF LENGTH(username) < 3 THEN
        error_message := 'Username must be at least 3 characters';
        validation_result := jsonb_set(validation_result, '{valid}', 'false');
        validation_result := jsonb_set(validation_result, '{errors}',
            (validation_result->'errors') || to_jsonb(error_message));
    END IF;
 
    -- Email validation
    IF email !~ '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$' THEN
        error_message := 'Invalid email format';
        validation_result := jsonb_set(validation_result, '{valid}', 'false');
        validation_result := jsonb_set(validation_result, '{errors}',
            (validation_result->'errors') || to_jsonb(error_message));
    END IF;
 
    -- Age validation
    IF age < 18 OR age > 120 THEN
        error_message := 'Age must be between 18-120 years';
        validation_result := jsonb_set(validation_result, '{valid}', 'false');
        validation_result := jsonb_set(validation_result, '{errors}',
            (validation_result->'errors') || to_jsonb(error_message));
    END IF;
 
    RETURN validation_result;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION check_password_strength(password TEXT)
RETURNS TEXT AS $$
DECLARE
    strength_score INTEGER := 0;
BEGIN
    -- Length check
    IF LENGTH(password) >= 8 THEN strength_score := strength_score + 1; END IF;
    IF LENGTH(password) >= 12 THEN strength_score := strength_score + 1; END IF;
 
    -- Complexity check
    IF password ~ '[A-Z]' THEN strength_score := strength_score + 1; END IF; -- Uppercase
    IF password ~ '[a-z]' THEN strength_score := strength_score + 1; END IF; -- Lowercase
    IF password ~ '[0-9]' THEN strength_score := strength_score + 1; END IF; -- Numbers
    IF password ~ '[^A-Za-z0-9]' THEN strength_score := strength_score + 1; END IF; -- Special chars
 
    -- Return strength level
    RETURN CASE strength_score
        WHEN 0 THEN 'Very Weak'
        WHEN 1 THEN 'Weak'
        WHEN 2 THEN 'Fair'
        WHEN 3 THEN 'Good'
        ELSE 'Very Strong'
    END;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION simple_encrypt(
    input_text TEXT,
    shift_key INTEGER
) RETURNS TEXT AS $$
DECLARE
    encrypted_text TEXT := '';
    i INTEGER;
    char_code INTEGER;
BEGIN
    FOR i IN 1..LENGTH(input_text) LOOP
        char_code := ASCII(SUBSTRING(input_text FROM i FOR 1));
        -- Simple Caesar cipher encryption
        encrypted_text := encrypted_text || CHR((char_code + shift_key) % 256);
    END LOOP;
 
    RETURN encrypted_text;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION parse_config(config_string TEXT)
RETURNS JSONB AS $$
DECLARE
    config_json JSONB := '{}';
    key_value_pair TEXT[];
    lines TEXT[];
    line TEXT;
BEGIN
    -- Split configuration string by lines
    lines := STRING_TO_ARRAY(config_string, E'\n');
 
    FOREACH line IN ARRAY lines LOOP
        -- Parse key-value pairs (ignore comments and empty lines)
        IF line != '' AND line NOT LIKE '#%' AND POSITION('=' IN line) > 0 THEN
            key_value_pair := STRING_TO_ARRAY(line, '=');
            config_json := jsonb_set(config_json, ARRAY[TRIM(key_value_pair[1])],
                to_jsonb(TRIM(key_value_pair[2])));
        END IF;
    END LOOP;
 
    RETURN config_json;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION calculate_statistics(numbers NUMERIC[])
RETURNS JSONB AS $$
DECLARE
    stats JSONB;
    total NUMERIC := 0;
    min_val NUMERIC;
    max_val NUMERIC;
    i INTEGER;
BEGIN
    IF array_length(numbers, 1) IS NULL THEN
        RETURN '{"count": 0, "error": "Empty array"}'::JSONB;
    END IF;
 
    -- Initialize
    min_val := numbers[1];
    max_val := numbers[1];
 
    -- Calculate statistics
    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;
 
    stats := jsonb_build_object(
        'count', array_length(numbers, 1),
        'sum', total,
        'average', total / array_length(numbers, 1),
        'min', min_val,
        'max', max_val,
        'range', max_val - min_val
    );
 
    RETURN stats;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION calculate_loan_payment(
    principal NUMERIC,
    annual_rate NUMERIC,
    years INTEGER
) RETURNS JSONB AS $$
DECLARE
    monthly_rate NUMERIC;
    num_payments INTEGER;
    monthly_payment NUMERIC;
    total_payment NUMERIC;
    total_interest NUMERIC;
BEGIN
    -- Input validation
    IF principal <= 0 OR annual_rate < 0 OR years <= 0 THEN
        RETURN '{"error": "Invalid input parameters"}'::JSONB;
    END IF;
 
    monthly_rate := annual_rate / 100 / 12;
    num_payments := years * 12;
 
    -- Calculate monthly payment using loan formula
    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;
    total_interest := total_payment - principal;
 
    RETURN jsonb_build_object(
        'monthly_payment', ROUND(monthly_payment, 2),
        'total_payment', ROUND(total_payment, 2),
        'total_interest', ROUND(total_interest, 2),
        'effective_rate', ROUND(annual_rate, 4)
    );
END;
$$ LANGUAGE plpgsql;
 
-- Usage
SELECT calculate_loan_payment(200000, 4.5, 30);

CREATE OR REPLACE FUNCTION analyze_text(input_text TEXT)
RETURNS JSONB AS $$
DECLARE
    word_count INTEGER;
    char_count INTEGER;
    sentence_count INTEGER;
    paragraph_count INTEGER;
    words TEXT[];
    avg_word_length NUMERIC;
BEGIN
    IF input_text IS NULL OR LENGTH(TRIM(input_text)) = 0 THEN
        RETURN '{"error": "Empty text provided"}'::JSONB;
    END IF;
 
    -- Basic counts
    char_count := LENGTH(input_text);
    
    -- Word analysis
    words := STRING_TO_ARRAY(REGEXP_REPLACE(input_text, '[^\w\s]', '', 'g'), ' ');
    words := ARRAY(SELECT unnest(words) WHERE unnest IS NOT NULL AND unnest != '');
    word_count := array_length(words, 1);
    
    -- Calculate average word length
    IF word_count > 0 THEN
        avg_word_length := (SELECT AVG(LENGTH(word)) FROM unnest(words) AS word);
    ELSE
        avg_word_length := 0;
    END IF;
 
    -- Sentence count (approximate)
    sentence_count := LENGTH(input_text) - LENGTH(REPLACE(REPLACE(REPLACE(input_text, '.', ''), '!', ''), '?', ''));
    
    -- Paragraph count (approximate)
    paragraph_count := LENGTH(input_text) - LENGTH(REPLACE(input_text, E'\n\n', '')) + 1;
 
    RETURN jsonb_build_object(
        'character_count', char_count,
        'word_count', COALESCE(word_count, 0),
        'sentence_count', sentence_count,
        'paragraph_count', paragraph_count,
        'average_word_length', ROUND(COALESCE(avg_word_length, 0), 2),
        'reading_time_minutes', ROUND(COALESCE(word_count, 0) / 200.0, 1) -- Assuming 200 words per minute
    );
END;
$$ LANGUAGE plpgsql;
 
-- Usage
SELECT analyze_text('This is a sample text for analysis. It contains multiple sentences! How does it perform?');

CREATE OR REPLACE FUNCTION process_json_array(json_data JSONB, operation TEXT DEFAULT 'sum')
RETURNS JSONB AS $$
DECLARE
    element JSONB;
    result NUMERIC := 0;
    count INTEGER := 0;
    min_val NUMERIC;
    max_val NUMERIC;
    current_val NUMERIC;
BEGIN
    -- Validate input
    IF json_data IS NULL OR jsonb_typeof(json_data) != 'array' THEN
        RETURN '{"error": "Invalid JSON array provided"}'::JSONB;
    END IF;
 
    -- Process each element
    FOR element IN SELECT jsonb_array_elements(json_data) LOOP
        IF jsonb_typeof(element) = 'number' THEN
            current_val := element::TEXT::NUMERIC;
            count := count + 1;
            
            CASE operation
                WHEN 'sum' THEN
                    result := result + current_val;
                WHEN 'product' THEN
                    IF count = 1 THEN result := current_val;
                    ELSE result := result * current_val;
                    END IF;
                ELSE
                    result := result + current_val; -- Default to sum
            END CASE;
            
            -- Track min/max
            IF count = 1 THEN
                min_val := current_val;
                max_val := current_val;
            ELSE
                IF current_val < min_val THEN min_val := current_val; END IF;
                IF current_val > max_val THEN max_val := current_val; END IF;
            END IF;
        END IF;
    END LOOP;
 
    IF count = 0 THEN
        RETURN '{"error": "No numeric values found in array"}'::JSONB;
    END IF;
 
    RETURN jsonb_build_object(
        'operation', operation,
        'result', result,
        'count', count,
        'average', ROUND(result / count, 4),
        'min', min_val,
        'max', max_val
    );
END;
$$ LANGUAGE plpgsql;
 
-- Usage
SELECT process_json_array('[1, 2, 3, 4, 5]'::JSONB, 'sum');
SELECT process_json_array('[2, 3, 4]'::JSONB, 'product');

CREATE OR REPLACE FUNCTION generate_random_string(
    length INTEGER DEFAULT 10,
    charset TEXT DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'
) RETURNS TEXT AS $$
DECLARE
    result TEXT := '';
    i INTEGER;
    random_index INTEGER;
BEGIN
    IF length <= 0 THEN
        RETURN '';
    END IF;
 
    FOR i IN 1..length LOOP
        random_index := FLOOR(RANDOM() * LENGTH(charset) + 1);
        result := result || SUBSTRING(charset FROM random_index FOR 1);
    END LOOP;
 
    RETURN result;
END;
$$ LANGUAGE plpgsql;
 
-- Usage
SELECT generate_random_string(8);
SELECT generate_random_string(12, '0123456789');

CREATE OR REPLACE FUNCTION safe_division(numerator NUMERIC, denominator NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF denominator = 0 THEN
        RAISE EXCEPTION 'Division by zero is not allowed';
    END IF;
    
    RETURN numerator / denominator;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Error in safe_division: %', SQLERRM;
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION validate_and_process(input_value TEXT)
RETURNS TEXT AS $$
BEGIN
    -- Comprehensive input validation
    IF input_value IS NULL THEN
        RAISE EXCEPTION 'Input cannot be NULL';
    END IF;
    
    IF LENGTH(TRIM(input_value)) = 0 THEN
        RAISE EXCEPTION 'Input cannot be empty';
    END IF;
    
    IF LENGTH(input_value) > 1000 THEN
        RAISE EXCEPTION 'Input too long (max 1000 characters)';
    END IF;
    
    -- Process the validated input
    RETURN UPPER(TRIM(input_value));
END;
$$ LANGUAGE plpgsql;

-- Use appropriate return types
CREATE OR REPLACE FUNCTION calculate_percentage(part NUMERIC, whole NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF whole = 0 THEN
        RETURN 0;
    END IF;
    
    RETURN ROUND((part / whole) * 100, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE; -- Mark as IMMUTABLE for better performance