User-Defined Functions (UDF) Management

CREATE [OR REPLACE] FUNCTION function_name ([parameter_list])
RETURNS return_type
AS $$
  # Python code or PL/pgSQL code
  return result
$$ LANGUAGE plpython3u; -- or plpgsql

CREATE OR REPLACE FUNCTION calculate_sum(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
  return a + b
$$ LANGUAGE plpython3u;

-- View all user-defined functions
SELECT 
    proname as function_name,
    pg_get_function_arguments(oid) as arguments,
    pg_get_function_result(oid) as return_type,
    prolang::regproc as language
FROM pg_proc
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY proname;

-- View complete function definition (including source code)
SELECT pg_get_functiondef(oid) as function_definition
FROM pg_proc
WHERE proname = 'calculate_sum';
 
-- Or use \df+ command (in psql)
\df+ calculate_sum

-- View detailed function information
SELECT
    p.proname as function_name,
    pg_get_function_arguments(p.oid) as parameters,
    pg_get_function_result(p.oid) as return_type,
    l.lanname as language,
    p.prosecdef as security_definer,
    p.provolatile as volatility,
    p.proisstrict as is_strict,
    p.proretset as returns_set,
    obj_description(p.oid, 'pg_proc') as description
FROM pg_proc p
JOIN pg_language l ON p.prolang = l.oid
WHERE p.proname = 'calculate_sum';

-- View objects that the function depends on
SELECT
    dep.classid::regclass,
    dep.objid::regproc,
    dep.objsubid,
    dep.refclassid::regclass,
    dep.refobjid,
    dep.refobjsubid,
    dep.deptype
FROM pg_depend dep
JOIN pg_proc p ON p.oid = dep.objid
WHERE p.proname = 'your_function_name';

DROP FUNCTION [IF EXISTS] function_name ([parameter_types]);

-- Drop parameterless function
DROP FUNCTION get_current_timestamp();
 
-- Safe deletion (if exists)
DROP FUNCTION IF EXISTS get_current_timestamp();

-- Drop function with parameters, need to specify parameter types
DROP FUNCTION calculate_sum(INTEGER, INTEGER);
 
-- Safe deletion
DROP FUNCTION IF EXISTS calculate_sum(INTEGER, INTEGER);

-- Assume there are two overloaded functions
CREATE FUNCTION process_data(data TEXT) RETURNS TEXT ...
CREATE FUNCTION process_data(data INTEGER) RETURNS TEXT ...
 
-- Drop specific version
DROP FUNCTION process_data(TEXT);    -- Drop the text processing version
DROP FUNCTION process_data(INTEGER); -- Drop the integer processing version

-- Delete all PL/Python functions
DO $$
DECLARE
    func_record RECORD;
BEGIN
    FOR func_record IN
        SELECT p.oid, p.proname, pg_get_function_identity_arguments(p.oid) as args
        FROM pg_proc p
        JOIN pg_language l ON p.prolang = l.oid
        WHERE l.lanname = 'plpython3u'
        AND p.pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
    LOOP
        EXECUTE format('DROP FUNCTION %I(%s)',
                      func_record.proname,
                      func_record.args);
    END LOOP;
END $$;

-- Modify function implementation
CREATE OR REPLACE FUNCTION calculate_sum(a INTEGER, b INTEGER)
RETURNS INTEGER
AS $$
  # Modified implementation
  result = a + b
  return result
$$ LANGUAGE plpython3u;

-- Modify function owner
ALTER FUNCTION add_numbers(INTEGER, INTEGER) OWNER TO new_owner;

-- View function permission information
SELECT
    proname as function_name,
    pronamespace::regnamespace as schema,
    proacl as privileges
FROM pg_proc
WHERE proname = 'calculate_sum';

-- Grant function execution permission to user
GRANT EXECUTE ON FUNCTION calculate_sum(INTEGER, INTEGER) TO username;
 
-- Grant function execution permission to role
GRANT EXECUTE ON FUNCTION calculate_sum(INTEGER, INTEGER) TO role_name;
 
-- Revoke execution permission
REVOKE EXECUTE ON FUNCTION calculate_sum(INTEGER, INTEGER) FROM username;

SELECT
    n.nspname as schema,
    p.proname as function_name,
    pg_get_function_arguments(p.oid) as parameters,
    pg_get_function_result(p.oid) as return_type,
    p.prosrc as source_code
FROM pg_proc p
JOIN pg_language l ON p.prolang = l.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE l.lanname = 'plpython3u'
ORDER BY n.nspname, p.proname;

-- Find functions using numpy
SELECT
    proname as function_name,
    pg_get_function_arguments(oid) as parameters
FROM pg_proc
WHERE prosrc LIKE '%import numpy%'
   OR prosrc LIKE '%from numpy%'
   OR prosrc LIKE '%numpy.%';

-- Use descriptive names
CREATE FUNCTION calculate_monthly_interest(...)
CREATE FUNCTION validate_email_format(...)
CREATE FUNCTION transform_user_data(...)

-- Include proper error handling
CREATE OR REPLACE FUNCTION safe_division(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    IF b = 0 THEN
        RAISE EXCEPTION 'Division by zero is not allowed';
    END IF;
    RETURN a / b;
END;
$$ LANGUAGE plpgsql;

-- Validate input parameters
CREATE OR REPLACE FUNCTION calculate_percentage(part NUMERIC, total NUMERIC)
RETURNS NUMERIC
AS $$
BEGIN
    IF part IS NULL OR total IS NULL THEN
        RETURN NULL;
    END IF;
    
    IF total <= 0 THEN
        RAISE EXCEPTION 'Total must be greater than zero';
    END IF;
    
    RETURN (part / total) * 100;
END;
$$ LANGUAGE plpgsql;

-- Add function comments
CREATE OR REPLACE FUNCTION calculate_compound_interest(
    principal NUMERIC,
    rate NUMERIC,
    periods INTEGER
)
RETURNS NUMERIC
AS $$
/*
Calculate compound interest using the formula: A = P(1 + r)^n
Parameters:
  principal - Initial amount
  rate - Interest rate (as decimal, e.g., 0.05 for 5%)
  periods - Number of compounding periods
Returns:
  Final amount after compound interest
*/
BEGIN
    IF principal <= 0 OR rate < 0 OR periods < 0 THEN
        RAISE EXCEPTION 'Invalid input parameters';
    END IF;
    
    RETURN principal * POWER(1 + rate, periods);
END;
$$ LANGUAGE plpgsql;
 
-- Add comment to function
COMMENT ON FUNCTION calculate_compound_interest(NUMERIC, NUMERIC, INTEGER) 
IS 'Calculates compound interest for given principal, rate, and periods';

-- Use appropriate volatility settings
CREATE OR REPLACE FUNCTION get_random_number()
RETURNS INTEGER
AS $$
    import random
    return random.randint(1, 100)
$$ LANGUAGE plpython3u VOLATILE; -- Changes every call
 
CREATE OR REPLACE FUNCTION calculate_tax_rate(amount NUMERIC)
RETURNS NUMERIC
AS $$
    -- Simple calculation that doesn't depend on external state
    if amount > 10000:
        return 0.25
    else:
        return 0.15
$$ LANGUAGE plpython3u IMMUTABLE; -- Same input always gives same output

-- Use SECURITY DEFINER carefully
CREATE OR REPLACE FUNCTION admin_operation()
RETURNS TEXT
AS $$
BEGIN
    -- Only allow specific users
    IF current_user NOT IN ('admin', 'manager') THEN
        RAISE EXCEPTION 'Access denied';
    END IF;
    
    -- Perform admin operation
    RETURN 'Operation completed';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create test functions
CREATE OR REPLACE FUNCTION test_calculate_sum()
RETURNS TEXT
AS $$
DECLARE
    result INTEGER;
    test_passed BOOLEAN := TRUE;
BEGIN
    -- Test case 1: Normal addition
    SELECT calculate_sum(5, 3) INTO result;
    IF result != 8 THEN
        test_passed := FALSE;
    END IF;
    
    -- Test case 2: Zero addition
    SELECT calculate_sum(0, 0) INTO result;
    IF result != 0 THEN
        test_passed := FALSE;
    END IF;
    
    -- Test case 3: Negative numbers
    SELECT calculate_sum(-5, 3) INTO result;
    IF result != -2 THEN
        test_passed := FALSE;
    END IF;
    
    IF test_passed THEN
        RETURN 'All tests passed';
    ELSE
        RETURN 'Tests failed';
    END IF;
END;
$$ LANGUAGE plpgsql;
 
-- Run tests
SELECT test_calculate_sum();