PostgreSQL教程

查看所有函数和存储过程

-- 查看当前数据库中的所有函数和存储过程
SELECT 
    proname AS function_name,
    pg_get_function_arguments(oid) AS arguments,
    pg_get_function_result(oid) AS return_type,
    prokind AS function_type,
    prosecdef AS is_definer,
    prosrc AS source_code
FROM pg_proc 
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
ORDER BY proname;

-- 查看所有用户定义的函数和存储过程
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type,
    CASE p.prokind
        WHEN 'f' THEN 'Function'
        WHEN 'p' THEN 'Procedure'
        WHEN 'a' THEN 'Aggregate'
        WHEN 'w' THEN 'Window'
    END AS object_type,
    pg_get_userbyid(p.proowner) AS owner
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, p.proname;

查询结果包含以下主要列：

• schema_name：函数或存储过程所属的模式名
• function_name：函数或存储过程的名称
• arguments：参数列表
• return_type：返回类型
• object_type：对象类型（Function、Procedure等）
• owner：所有者

查看特定模式中的函数和存储过程

-- 查看特定模式中的所有函数
SELECT 
    proname AS function_name,
    pg_get_function_arguments(oid) AS arguments,
    pg_get_function_result(oid) AS return_type,
    prokind AS function_type,
    prosecdef AS is_definer,
    prosrc AS source_code
FROM pg_proc 
WHERE pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'myschema')
ORDER BY proname;

-- 查看特定名称的函数或存储过程
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS arguments,
    pg_get_function_result(p.oid) AS return_type,
    pg_get_functiondef(p.oid) AS function_definition,
    obj_description(p.oid) AS description
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname = 'calculate_discount';

查询information_schema.Routines表

-- 查询所有函数和存储过程的基本信息
SELECT 
    routine_schema,
    routine_name,
    routine_type,
    data_type,
    created,
    last_altered
FROM information_schema.routines
WHERE routine_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY routine_schema, routine_name;

-- 查询特定模式中的函数
SELECT 
    routine_name,
    routine_type,
    data_type,
    routine_definition,
    created,
    last_altered
FROM information_schema.routines
WHERE routine_schema = 'public'
ORDER BY routine_name;

-- 查询特定函数的完整信息
SELECT *
FROM information_schema.routines
WHERE routine_schema = 'public' 
  AND routine_name = 'calculate_discount';

-- 搜索包含特定关键词的函数
SELECT 
    routine_schema,
    routine_name,
    routine_type
FROM information_schema.routines
WHERE routine_definition ILIKE '%employee%';

使用pg_get_functiondef函数

-- 获取函数的完整定义
SELECT pg_get_functiondef(oid) 
FROM pg_proc 
WHERE proname = 'calculate_discount';

-- 获取带有模式名的函数定义
SELECT pg_get_functiondef(p.oid) 
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public' AND p.proname = 'calculate_discount';

-- 获取所有用户定义函数的定义
SELECT 
    n.nspname || '.' || p.proname AS function_name,
    pg_get_functiondef(p.oid) AS function_definition
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND p.prokind = 'f'  -- 只获取函数，不包括存储过程
ORDER BY n.nspname, p.proname;

查看函数参数信息

-- 查看函数的参数信息
SELECT 
    n.nspname AS schema_name,
    p.proname AS function_name,
    pg_get_function_arguments(p.oid) AS argument_types,
    pg_get_function_result(p.oid) AS return_type,
    proargnames AS argument_names,
    proargmodes AS argument_modes
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE p.proname = 'calculate_discount';

-- 从information_schema.Parameters表查询参数信息
SELECT 
    specific_schema,
    specific_name,
    parameter_name,
    parameter_mode,
    data_type,
    ordinal_position
FROM information_schema.parameters
WHERE specific_schema = 'public' 
  AND specific_name = 'calculate_discount'
ORDER BY ordinal_position;

psql中的查看命令

-- 列出所有函数（不包括系统函数）
\df

-- 列出特定模式中的函数
\df public.*

-- 列出名称匹配模式的函数
\df *calculate*

-- 列出所有函数，包括系统函数
\df+

-- 显示函数的定义
\sf function_name

-- 显示特定模式中函数的定义
\sf schema_name.function_name

-- 显示函数定义并保存到文件
\sf function_name \o function_definition.sql

-- 列出所有存储过程（PostgreSQL 11+）
\dfp

-- 列出特定模式中的存储过程
\dfp public.*

-- 显示存储过程的定义
\sf procedure_name

查询函数依赖

-- 查看函数被哪些其他对象依赖
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
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 
    n.nspname AS schema_name,
    p.proname AS function_name,
    d.deptype AS dependency_type,
    referenced_ns.nspname AS referenced_schema,
    referenced_proc.proname AS referenced_function
FROM pg_depend d
JOIN pg_proc p ON d.objid = p.oid
JOIN pg_namespace n ON p.pronamespace = n.oid
LEFT JOIN pg_proc referenced_proc ON d.refobjid = referenced_proc.oid
LEFT JOIN pg_namespace referenced_ns ON referenced_proc.pronamespace = referenced_ns.oid
WHERE p.proname = 'complex_function';

在Python中查看函数信息

import psycopg2

# 连接到PostgreSQL数据库
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="username",
    password="password"
)

# 创建游标对象
cur = conn.cursor()

# 查看所有用户定义的函数
cur.execute("""
    SELECT 
        n.nspname AS schema_name,
        p.proname AS function_name,
        pg_get_function_arguments(p.oid) AS arguments,
        pg_get_function_result(p.oid) AS return_type
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
    ORDER BY n.nspname, p.proname
""")

# 输出函数列表
print("函数列表：")
for row in cur.fetchall():
    print(f"- {row[0]}.{row[1]}({row[2]}) -> {row[3]}")

# 查看特定函数的定义
function_name = "calculate_discount"
cur.execute("""
    SELECT pg_get_functiondef(oid) 
    FROM pg_proc 
    WHERE proname = %s
""", (function_name,))

result = cur.fetchone()
if result:
    print(f"\n函数 '{function_name}' 的定义：")
    print(result[0])

# 关闭连接
cur.close()
conn.close()

在Java中查看函数信息

import java.sql.*;

public class ViewPostgreSQLFunctions {
    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)) {
            // 查看所有用户定义的函数
            PreparedStatement stmt = conn.prepareStatement("""
                SELECT 
                    n.nspname AS schema_name,
                    p.proname AS function_name,
                    pg_get_function_arguments(p.oid) AS arguments,
                    pg_get_function_result(p.oid) AS return_type
                FROM pg_proc p
                JOIN pg_namespace n ON p.pronamespace = n.oid
                WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
                ORDER BY n.nspname, p.proname
            """);
            
            ResultSet rs = stmt.executeQuery();
            
            // 输出函数列表
            System.out.println("函数列表：");
            while (rs.next()) {
                String schema = rs.getString("schema_name");
                String name = rs.getString("function_name");
                String args = rs.getString("arguments");
                String returnType = rs.getString("return_type");
                System.out.println("- " + schema + "." + name + "(" + args + ") -> " + returnType);
            }
            
            // 查看特定函数的定义
            String functionName = "calculate_discount";
            PreparedStatement stmt2 = conn.prepareStatement(
                "SELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = ?"
            );
            stmt2.setString(1, functionName);
            ResultSet rs2 = stmt2.executeQuery();
            
            if (rs2.next()) {
                String definition = rs2.getString(1);
                System.out.println("\n函数 '" + functionName + "' 的定义：");
                System.out.println(definition);
            }
            
            // 关闭资源
            rs.close();
            stmt.close();
            rs2.close();
            stmt2.close();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}