PostgreSQL教程

使用CREATE OR REPLACE FUNCTION修改函数

-- 原始函数
CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00
)
RETURNS NUMERIC(10,2) AS $$
BEGIN
    RETURN price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

-- 修改函数定义（添加更多功能）
CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00,
    max_discount NUMERIC(10,2) DEFAULT 100.00
)
RETURNS NUMERIC(10,2) AS $$
DECLARE
    discount_amount NUMERIC(10,2);
BEGIN
    discount_amount := price * (discount_percent / 100);
    
    -- 限制最大折扣金额
    IF discount_amount > max_discount THEN
        discount_amount := max_discount;
    END IF;
    
    RETURN price - discount_amount;
END;
$$ LANGUAGE plpgsql;

使用CREATE OR REPLACE FUNCTION的优点：

• 保留现有的权限设置
• 保留现有的依赖关系
• 如果函数不存在会自动创建
• 原子操作，不会出现函数不存在的中间状态

使用CREATE OR REPLACE PROCEDURE修改存储过程

-- 原始存储过程
CREATE OR REPLACE PROCEDURE create_user(
    p_username VARCHAR(50),
    p_email VARCHAR(255),
    p_first_name VARCHAR(50),
    p_last_name VARCHAR(50)
)
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO users (username, email, first_name, last_name, created_at)
    VALUES (p_username, p_email, p_first_name, p_last_name, CURRENT_TIMESTAMP);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- 修改存储过程（添加更多验证）
CREATE OR REPLACE PROCEDURE create_user(
    p_username VARCHAR(50),
    p_email VARCHAR(255),
    p_first_name VARCHAR(50),
    p_last_name VARCHAR(50),
    p_phone VARCHAR(20) DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 输入验证
    IF p_username IS NULL OR LENGTH(TRIM(p_username)) = 0 THEN
        RAISE EXCEPTION 'Username cannot be empty';
    END IF;
    
    IF p_email IS NULL OR p_email !~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format';
    END IF;
    
    -- 检查用户是否已存在
    IF EXISTS (SELECT 1 FROM users WHERE username = p_username OR email = p_email) THEN
        RAISE EXCEPTION 'User with this username or email already exists';
    END IF;
    
    -- 插入新用户
    INSERT INTO users (username, email, first_name, last_name, phone, created_at)
    VALUES (p_username, p_email, p_first_name, p_last_name, p_phone, CURRENT_TIMESTAMP);
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

修改函数的所有者

-- 修改函数所有者
ALTER FUNCTION calculate_discount(NUMERIC, NUMERIC) OWNER TO new_owner;

-- 修改存储过程所有者
ALTER PROCEDURE create_user(VARCHAR, VARCHAR, VARCHAR, VARCHAR) OWNER TO new_owner;

修改函数的权限

-- 授予函数执行权限
GRANT EXECUTE ON FUNCTION calculate_discount(NUMERIC, NUMERIC) TO user_role;

-- 撤销函数执行权限
REVOKE EXECUTE ON FUNCTION calculate_discount(NUMERIC, NUMERIC) FROM user_role;

-- 授予所有函数的执行权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO user_role;

-- 授予存储过程执行权限
GRANT EXECUTE ON PROCEDURE create_user(VARCHAR, VARCHAR, VARCHAR, VARCHAR) TO user_role;

添加或修改注释

-- 为函数添加注释
COMMENT ON FUNCTION calculate_discount(NUMERIC, NUMERIC) IS '计算商品折扣价格的函数';

-- 为存储过程添加注释
COMMENT ON PROCEDURE create_user(VARCHAR, VARCHAR, VARCHAR, VARCHAR) IS '创建新用户的存储过程';

-- 为函数参数添加注释
COMMENT ON PARAMETER calculate_discount.price IS '商品原价';
COMMENT ON PARAMETER calculate_discount.discount_percent IS '折扣百分比';

设置函数的配置参数

-- 创建带配置参数的函数
CREATE OR REPLACE FUNCTION get_user_timezone()
RETURNS TEXT AS $$
BEGIN
    RETURN current_setting('timezone');
END;
$$ LANGUAGE plpgsql
SET timezone = 'Asia/Shanghai';

-- 修改函数的配置参数
ALTER FUNCTION get_user_timezone() SET timezone = 'UTC';

-- 重置函数的配置参数
ALTER FUNCTION get_user_timezone() RESET timezone;

修改函数的安全性设置

-- 创建定义者权限的函数
CREATE OR REPLACE FUNCTION admin_function()
RETURNS VOID AS $$
BEGIN
    -- 需要管理员权限的操作
    DELETE FROM logs WHERE created_at < CURRENT_DATE - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;

-- 修改函数为调用者权限
ALTER FUNCTION admin_function() SECURITY INVOKER;

-- 修改函数为定义者权限
ALTER FUNCTION admin_function() SECURITY DEFINER;

查看和处理依赖关系

-- 查看函数的依赖关系
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    d.deptype AS dependency_type,
    dependent_ns.nspname AS dependent_schema,
    dependent_class.relname AS dependent_object
FROM pg_depend d
JOIN pg_proc p ON d.refobjid = p.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_class dependent_class ON d.objid = dependent_class.oid
LEFT JOIN pg_namespace dependent_ns ON dependent_class.relnamespace = dependent_ns.oid
WHERE p.proname = 'calculate_discount';

-- 查看哪些对象依赖于函数
SELECT 
    dependent_ns.nspname AS dependent_schema,
    dependent_proc.proname AS dependent_function
FROM pg_depend d
JOIN pg_proc p ON d.refobjid = p.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_proc dependent_proc ON d.objid = dependent_proc.oid
LEFT JOIN pg_namespace dependent_ns ON dependent_proc.pronamespace = dependent_ns.oid
WHERE n.nspname = 'public' AND p.proname = 'calculate_discount';

-- 安全地修改函数（先检查依赖）
DO $$
DECLARE
    dependency_count INTEGER;
BEGIN
    -- 检查函数是否有依赖
    SELECT COUNT(*) INTO dependency_count
    FROM pg_depend d
    JOIN pg_proc p ON d.refobjid = p.oid
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public' AND p.proname = 'calculate_discount';
    
    IF dependency_count > 0 THEN
        RAISE NOTICE '函数有 % 个依赖对象，修改前请确认', dependency_count;
    ELSE
        -- 执行修改操作
        EXECUTE 'CREATE OR REPLACE FUNCTION calculate_discount(
            price NUMERIC(10,2), 
            discount_percent NUMERIC(5,2) DEFAULT 10.00
        )
        RETURNS NUMERIC(10,2) AS $$
        BEGIN
            RETURN price * (1 - discount_percent / 100);
        END;
        $$ LANGUAGE plpgsql';
    END IF;
END;
$$;

函数版本控制示例

-- 创建带版本号的函数
CREATE OR REPLACE FUNCTION calculate_discount_v2(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00,
    customer_tier VARCHAR(20) DEFAULT 'regular'
)
RETURNS NUMERIC(10,2) AS $$
DECLARE
    final_discount NUMERIC(5,2);
BEGIN
    final_discount := discount_percent;
    
    -- 根据客户等级调整折扣
    CASE customer_tier
        WHEN 'premium' THEN
            final_discount := final_discount + 5.00;
        WHEN 'vip' THEN
            final_discount := final_discount + 10.00;
        ELSE
            -- regular客户保持原折扣
            NULL;
    END CASE;
    
    -- 确保折扣不超过50%
    IF final_discount > 50.00 THEN
        final_discount := 50.00;
    END IF;
    
    RETURN price * (1 - final_discount / 100);
END;
$$ LANGUAGE plpgsql;

-- 为旧版本函数添加弃用标记
CREATE OR REPLACE FUNCTION calculate_discount(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00
)
RETURNS NUMERIC(10,2) AS $$
BEGIN
    RAISE WARNING 'calculate_discount函数已被弃用，请使用calculate_discount_v2';
    RETURN price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql;

使用Python动态修改函数

import psycopg2

def modify_function(connection, function_name, new_definition):
    """
    动态修改PostgreSQL函数
    """
    cursor = connection.cursor()
    
    try:
        # 构造修改函数的SQL语句
        sql = f"CREATE OR REPLACE FUNCTION {function_name} {new_definition}"
        
        # 执行修改
        cursor.execute(sql)
        connection.commit()
        
        print(f"函数 {function_name} 修改成功")
        
    except Exception as e:
        connection.rollback()
        print(f"修改函数时出错: {e}")
        
    finally:
        cursor.close()

# 使用示例
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="username",
    password="password"
)

new_function_def = """(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00
)
RETURNS NUMERIC(10,2) AS $$
BEGIN
    RETURN price * (1 - discount_percent / 100);
END;
$$ LANGUAGE plpgsql"""

modify_function(conn, "calculate_discount", new_function_def)
conn.close()