CREATE [OR REPLACE] FUNCTION function_name ([parameter_list])
  RETURNS return_type
AS $$
  # Python 代码
  return result
$$ LANGUAGE plpython3u;

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

-- 查看所有用户自定义函数
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;

-- 查看函数的完整定义（包括源代码）
SELECT pg_get_functiondef(oid) as function_definition
FROM pg_proc
WHERE proname = 'calculate_sum';
 
-- 或者使用 \df+ 命令（在 psql 中）
\df+ calculate_sum

-- 查看函数的详细信息
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';

-- 查看函数依赖的对象
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 FUNCTION get_current_timestamp();
 
-- 安全删除（如果存在）
DROP FUNCTION IF EXISTS get_current_timestamp();

-- 删除带参数函数，需要指定参数类型
DROP FUNCTION calculate_sum(INTEGER, INTEGER);
 
-- 安全删除
DROP FUNCTION IF EXISTS calculate_sum(INTEGER, INTEGER);

-- 假设有两个重载函数
CREATE FUNCTION process_data(data TEXT) RETURNS TEXT ...
CREATE FUNCTION process_data(data INTEGER) RETURNS TEXT ...
 
-- 删除特定版本
DROP FUNCTION process_data(TEXT);    -- 删除处理文本的版本
DROP FUNCTION process_data(INTEGER); -- 删除处理整数的版本

-- 删除所有 PL/Python 函数
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 $$;

-- 修改函数实现
CREATE OR REPLACE FUNCTION calculate_sum(a INTEGER, b INTEGER)
  RETURNS INTEGER
AS $$
  # 修改后的实现
  result = a + b
  return result
$$ LANGUAGE plpython3u;

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

-- 查看函数的权限信息
SELECT
    proname as function_name,
    pronamespace::regnamespace as schema,
    proacl as privileges
FROM pg_proc
WHERE proname = 'calculate_sum';

-- 授予函数执行权限给用户
GRANT EXECUTE ON FUNCTION calculate_sum(INTEGER, INTEGER) TO username;
 
-- 授予函数执行权限给角色
GRANT EXECUTE ON FUNCTION calculate_sum(INTEGER, INTEGER) TO role_name;
 
-- 撤销执行权限
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;

-- 查找使用 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.%';