PostgreSQL教程

基本删除语法

-- 删除指定函数
DROP FUNCTION function_name(parameter_types);

-- 示例：删除calculate_discount函数
DROP FUNCTION calculate_discount(NUMERIC, NUMERIC);

-- 删除函数时不抛出错误（如果函数不存在）
DROP FUNCTION IF EXISTS calculate_discount(NUMERIC, NUMERIC);

-- 删除带默认参数的函数
DROP FUNCTION IF EXISTS calculate_discount(
    price NUMERIC(10,2), 
    discount_percent NUMERIC(5,2) DEFAULT 10.00
);

删除函数时需要注意：

• 必须指定完整的参数列表以唯一标识函数
• 如果函数不存在，不使用IF EXISTS会导致错误
• 删除函数会同时删除相关的权限和注释

删除存储过程语法

-- 删除指定存储过程
DROP PROCEDURE procedure_name(parameter_types);

-- 示例：删除create_user存储过程
DROP PROCEDURE create_user(VARCHAR, VARCHAR, VARCHAR, VARCHAR);

-- 删除存储过程时不抛出错误（如果存储过程不存在）
DROP PROCEDURE IF EXISTS create_user(VARCHAR, VARCHAR, VARCHAR, VARCHAR);

-- 删除带默认参数的存储过程
DROP PROCEDURE IF EXISTS create_user(
    p_username VARCHAR(50),
    p_email VARCHAR(255),
    p_first_name VARCHAR(50),
    p_last_name VARCHAR(50)
);

检查函数依赖关系

-- 查看哪些对象依赖于指定函数
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    d.deptype AS dependency_type,
    CASE d.deptype
        WHEN 'n' THEN 'Normal'
        WHEN 'a' THEN 'Automatic'
        WHEN 'i' THEN 'Internal'
        WHEN 'P' THEN 'Pinned'
        WHEN 'e' THEN 'Extension'
    END AS dependency_description,
    dependent_ns.nspname AS dependent_schema,
    dependent_class.relname AS dependent_object,
    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_class dependent_class ON d.objid = dependent_class.oid
LEFT JOIN pg_namespace dependent_ns ON dependent_class.relnamespace = dependent_ns.oid
LEFT JOIN pg_proc dependent_proc ON d.objid = dependent_proc.oid
WHERE n.nspname = 'public' AND p.proname = 'calculate_discount';

-- 查看函数被哪些视图或触发器使用
SELECT 
    dependent_ns.nspname AS dependent_schema,
    dependent_class.relname AS dependent_object,
    dependent_class.relkind AS object_type
FROM pg_depend d
JOIN pg_proc p ON d.refobjid = p.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_class dependent_class ON d.objid = dependent_class.oid
JOIN pg_namespace dependent_ns ON dependent_class.relnamespace = dependent_ns.oid
WHERE n.nspname = 'public' 
  AND p.proname = 'calculate_discount'
  AND dependent_class.relkind IN ('v', 'm');  -- v=视图, m=物化视图

级联删除依赖对象

-- 级联删除函数及其依赖对象（谨慎使用）
DROP FUNCTION calculate_discount(NUMERIC, NUMERIC) CASCADE;

-- 级联删除存储过程及其依赖对象
DROP PROCEDURE create_user(VARCHAR, VARCHAR, VARCHAR, VARCHAR) CASCADE;

-- 注意：CASCADE会删除所有依赖于该函数的对象，可能导致数据丢失

CASCADE选项的风险：

• 可能删除重要的视图、触发器或其他函数
• 可能导致应用程序功能异常
• 在生产环境中使用前必须仔细评估

检查和确认删除

-- 创建安全删除函数的存储过程
CREATE OR REPLACE PROCEDURE safe_drop_function(
    function_name TEXT,
    schema_name TEXT DEFAULT 'public'
)
LANGUAGE plpgsql
AS $$
DECLARE
    function_oid OID;
    dependency_count INTEGER;
BEGIN
    -- 检查函数是否存在
    SELECT oid INTO function_oid
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = schema_name AND p.proname = function_name;
    
    IF function_oid IS NULL THEN
        RAISE NOTICE '函数 %.% 不存在', schema_name, function_name;
        RETURN;
    END IF;
    
    -- 检查依赖关系
    SELECT COUNT(*) INTO dependency_count
    FROM pg_depend
    WHERE refobjid = function_oid;
    
    IF dependency_count > 0 THEN
        RAISE NOTICE '函数 %.% 有 % 个依赖对象，无法安全删除', 
            schema_name, function_name, dependency_count;
        -- 列出依赖对象
        FOR rec IN 
            SELECT 
                dependent_ns.nspname AS dependent_schema,
                dependent_class.relname AS dependent_object
            FROM pg_depend d
            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 d.refobjid = function_oid
        LOOP
            RAISE NOTICE '  依赖对象: %.%', rec.dependent_schema, rec.dependent_object;
        END LOOP;
    ELSE
        -- 安全删除函数
        EXECUTE format('DROP FUNCTION %I.%I', schema_name, function_name);
        RAISE NOTICE '函数 %.% 已成功删除', schema_name, function_name;
    END IF;
END;
$$;

-- 使用安全删除存储过程
CALL safe_drop_function('calculate_discount');

批量删除示例

-- 删除特定模式中的所有用户定义函数（谨慎使用）
DO $$
DECLARE
    func_record RECORD;
BEGIN
    FOR func_record IN 
        SELECT 
            n.nspname AS schema_name,
            p.proname AS function_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE n.nspname = 'temp_functions'  -- 指定要删除函数的模式
          AND p.prokind = 'f'  -- 只删除函数，不包括存储过程
    LOOP
        -- 检查依赖关系
        IF NOT EXISTS (
            SELECT 1 FROM pg_depend WHERE refobjid = (
                SELECT oid FROM pg_proc p
                JOIN pg_namespace n ON p.pronamespace = n.oid
                WHERE n.nspname = func_record.schema_name 
                  AND p.proname = func_record.function_name
            )
        ) THEN
            EXECUTE format('DROP FUNCTION %I.%I(%s)', 
                func_record.schema_name, 
                func_record.function_name, 
                func_record.args);
            RAISE NOTICE '已删除函数: %.%(%s)', 
                func_record.schema_name, 
                func_record.function_name, 
                func_record.args;
        ELSE
            RAISE NOTICE '函数 %.%(%s) 有依赖关系，跳过删除', 
                func_record.schema_name, 
                func_record.function_name, 
                func_record.args;
        END IF;
    END LOOP;
END;
$$;

-- 删除名称匹配模式的函数
DO $$
DECLARE
    func_record RECORD;
BEGIN
    FOR func_record IN 
        SELECT 
            n.nspname AS schema_name,
            p.proname AS function_name,
            pg_get_function_identity_arguments(p.oid) AS args
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE p.proname LIKE 'temp_%'  -- 删除名称以temp_开头的函数
    LOOP
        EXECUTE format('DROP FUNCTION IF EXISTS %I.%I(%s)', 
            func_record.schema_name, 
            func_record.function_name, 
            func_record.args);
    END LOOP;
END;
$$;

权限处理

-- 查看函数的权限
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_userbyid(p.proowner) AS owner,
    array_agg(pr.perm) AS permissions,
    array_agg(pr.grantee) AS grantees
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN (
    SELECT 
        objid,
        privilege_type AS perm,
        grantee
    FROM information_schema.routine_privileges
) pr ON p.oid = pr.objid
WHERE n.nspname = 'public' AND p.proname = 'calculate_discount'
GROUP BY n.nspname, p.proname, p.proowner;

-- 删除函数后，相关权限自动清除
DROP FUNCTION calculate_discount(NUMERIC, NUMERIC);

-- 验证权限已被删除
SELECT COUNT(*) FROM information_schema.routine_privileges 
WHERE routine_name = 'calculate_discount';  -- 应该返回0

Python中删除函数

import psycopg2
from psycopg2 import sql

def drop_function_safely(connection, schema_name, function_name, parameter_types):
    """
    安全地删除PostgreSQL函数
    """
    cursor = connection.cursor()
    
    try:
        # 检查函数是否存在
        check_sql = """
            SELECT COUNT(*) 
            FROM pg_proc p
            JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE n.nspname = %s AND p.proname = %s
        """
        cursor.execute(check_sql, (schema_name, function_name))
        exists = cursor.fetchone()[0] > 0
        
        if not exists:
            print(f"函数 {schema_name}.{function_name} 不存在")
            return False
        
        # 检查依赖关系
        dependency_sql = """
            SELECT 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 = %s AND p.proname = %s
        """
        cursor.execute(dependency_sql, (schema_name, function_name))
        dependency_count = cursor.fetchone()[0]
        
        if dependency_count > 0:
            print(f"函数 {schema_name}.{function_name} 有 {dependency_count} 个依赖对象，无法安全删除")
            return False
        
        # 删除函数
        drop_sql = sql.SQL("DROP FUNCTION {}.{}({})").format(
            sql.Identifier(schema_name),
            sql.Identifier(function_name),
            sql.SQL(parameter_types)
        )
        cursor.execute(drop_sql)
        connection.commit()
        
        print(f"函数 {schema_name}.{function_name} 已成功删除")
        return True
        
    except Exception as e:
        connection.rollback()
        print(f"删除函数时出错: {e}")
        return False
        
    finally:
        cursor.close()

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

drop_function_safely(conn, "public", "calculate_discount", "NUMERIC, NUMERIC")
conn.close()

Java中删除函数

import java.sql.*;

public class DropPostgreSQLFunction {
    public static boolean dropFunctionSafely(
            Connection conn, 
            String schemaName, 
            String functionName, 
            String parameterTypes) {
        
        try {
            // 检查函数是否存在
            PreparedStatement checkStmt = conn.prepareStatement("""
                SELECT COUNT(*) 
                FROM pg_proc p
                JOIN pg_namespace n ON p.pronamespace = n.oid
                WHERE n.nspname = ? AND p.proname = ?
            """);
            checkStmt.setString(1, schemaName);
            checkStmt.setString(2, functionName);
            ResultSet checkRs = checkStmt.executeQuery();
            checkRs.next();
            boolean exists = checkRs.getInt(1) > 0;
            checkRs.close();
            checkStmt.close();
            
            if (!exists) {
                System.out.println("函数 " + schemaName + "." + functionName + " 不存在");
                return false;
            }
            
            // 检查依赖关系
            PreparedStatement depStmt = conn.prepareStatement("""
                SELECT 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 = ? AND p.proname = ?
            """);
            depStmt.setString(1, schemaName);
            depStmt.setString(2, functionName);
            ResultSet depRs = depStmt.executeQuery();
            depRs.next();
            int dependencyCount = depRs.getInt(1);
            depRs.close();
            depStmt.close();
            
            if (dependencyCount > 0) {
                System.out.println("函数 " + schemaName + "." + functionName + 
                    " 有 " + dependencyCount + " 个依赖对象，无法安全删除");
                return false;
            }
            
            // 删除函数
            String dropSql = "DROP FUNCTION " + schemaName + "." + functionName + 
                           "(" + parameterTypes + ")";
            Statement dropStmt = conn.createStatement();
            dropStmt.execute(dropSql);
            dropStmt.close();
            
            System.out.println("函数 " + schemaName + "." + functionName + " 已成功删除");
            return true;
            
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException rollbackEx) {
                rollbackEx.printStackTrace();
            }
            return false;
        }
    }
    
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/mydb";
        String user = "username";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            dropFunctionSafely(conn, "public", "calculate_discount", "NUMERIC, NUMERIC");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}