如何确定PostgreSQL数据库对象类型及识别包的方法

1. 单查询高效识别对象类型 #

可以通过UNION ALL将多类对象的查询合并为一次请求，避免多次查询和条件判断，直接返回目标对象的类型。以下是两种高效的单查询方案：

方案一：COALESCE 嵌套查询 #

直接按优先级匹配对象类型，无匹配时返回Unknown：

SELECT COALESCE(
  -- 匹配表/视图类对象
  (SELECT CASE c.relkind
           WHEN 'r' THEN 'Table'
           WHEN 'v' THEN 'View'
           WHEN 'f' THEN 'Foreign Table'
           WHEN 'm' THEN 'Materialized View'
           WHEN 'p' THEN 'Partitioned Table'
         END
   FROM pg_class c
   JOIN pg_namespace n ON n.oid = c.relnamespace
   WHERE n.nspname = %s AND c.relname = %s),
  -- 匹配函数/存储过程
  (SELECT CASE r.routine_type
           WHEN 'FUNCTION' THEN 'Function'
           WHEN 'PROCEDURE' THEN 'Procedure'
         END
   FROM information_schema.routines r
   WHERE r.specific_schema = %s AND r.routine_name = %s),
  -- 无匹配时返回默认值
  'Unknown'
) AS object_type;

方案二：UNION ALL + LIMIT 1 #

通过子查询合并所有可能的对象类型，只取第一个匹配结果，性能更优：

SELECT object_type
FROM (
  -- 表/视图类对象
  SELECT CASE c.relkind
           WHEN 'r' THEN 'Table'
           WHEN 'v' THEN 'View'
           WHEN 'f' THEN 'Foreign Table'
           WHEN 'm' THEN 'Materialized View'
           WHEN 'p' THEN 'Partitioned Table'
         END AS object_type
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = %s AND c.relname = %s
  
  UNION ALL
  
  -- 函数/存储过程
  SELECT CASE r.routine_type
           WHEN 'FUNCTION' THEN 'Function'
           WHEN 'PROCEDURE' THEN 'Procedure'
         END AS object_type
  FROM information_schema.routines r
  WHERE r.specific_schema = %s AND r.routine_name = %s
) AS combined_results
WHERE object_type IS NOT NULL
LIMIT 1;

使用时只需执行一次查询，直接获取结果即可，无需额外的条件判断逻辑。

2. PostgreSQL中识别"包" #

PostgreSQL没有像Oracle那样的原生包(Package)，通常通过两种方式模拟包的功能，对应不同的识别方法：

方式一：识别模拟包的模式(Schema) #

很多开发者会用独立的模式存放一组相关的函数、存储过程和对象，以此模拟包的逻辑。要判断某个模式是否作为"包"使用，可以查询该模式下的对象集合：

SELECT n.nspname AS package_schema,
       count(*) AS object_count,
       array_agg(DISTINCT CASE
         WHEN c.relkind IN ('r','v','m','p','f') THEN 'TABLE/VIEW'
         WHEN r.routine_type IS NOT NULL THEN r.routine_type
       END) AS contained_object_types
FROM pg_namespace n
LEFT JOIN pg_class c ON c.relnamespace = n.oid
LEFT JOIN information_schema.routines r ON r.specific_schema = n.nspname
WHERE n.nspname = %s
GROUP BY n.nspname;

如果返回结果包含多个相关的函数、表对象，那么该模式大概率是用来模拟包的。

方式二：识别扩展(Extension) #

PostgreSQL的扩展是预打包的功能集合，类似其他数据库的包。要查询扩展或判断对象是否属于扩展，可使用pg_extension表：

-- 查询指定扩展的信息
SELECT extname AS extension_name,
       extversion AS version,
       n.nspname AS extension_schema
FROM pg_extension e
JOIN pg_namespace n ON e.extnamespace = n.oid
WHERE extname = %s;

-- 判断某个对象是否属于扩展
SELECT e.extname AS part_of_extension
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
LEFT JOIN pg_extension e ON e.extnamespace = n.oid
WHERE n.nspname = %s AND c.relname = %s;

内容的提问来源于stack exchange，提问作者Fatema_