PostgreSQL中如何自动获取指定表的Schema以优化存储过程

我编写的存储过程如下：

CREATE OR REPLACE PROCEDURE c_sch.COMPUTETABLECOMMENT (p_BASETABLENAME in VARCHAR, p_sCOMMENT in out VARCHAR)
AS $$ 
DECLARE
  v_sComment VARCHAR;
BEGIN
  p_sCOMMENT:='Logtable for '||p_BASETABLENAME;
  select obj_description('myschema'.p_BASETABLENAME ::regclass, 'pg_class') into into v_sComment;
  
  IF v_sComment is not NULL THEN
    p_sCOMMENT:=substr(c_sch.ExpandComment(p_sCOMMENT||': '||v_sComment),1,4000);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_sCOMMENT:='Logtable for '||p_BASETABLENAME;
END;
$$ LANGUAGE plpgsql

希望该存储过程无需手动修改myschema即可适配不同的表运行。

是否存在获取指定表的Schema的方法？

解决方案 #

PostgreSQL可以通过系统目录或regclass类型自动处理表的schema归属，以下两种方案可以解决你的问题：

方案1：从系统目录查询表的Schema

通过pg_class和pg_namespace系统表关联查询，获取输入表对应的schema名称，再动态拼接对象名获取注释。同时修正原代码中into into的语法错误：

CREATE OR REPLACE PROCEDURE c_sch.COMPUTETABLECOMMENT (p_BASETABLENAME in VARCHAR, p_sCOMMENT in out VARCHAR)
AS $$ 
DECLARE
  v_sSchema VARCHAR;
  v_sComment VARCHAR;
BEGIN
  p_sCOMMENT := 'Logtable for ' || p_BASETABLENAME;

  -- 查询表所属的schema
  SELECT n.nspname
  INTO v_sSchema
  FROM pg_class c
  JOIN pg_namespace n ON c.relnamespace = n.oid
  WHERE c.relname = p_BASETABLENAME
    AND c.relkind = 'r'; -- 仅匹配普通表

  -- 获取原表注释
  IF v_sSchema IS NOT NULL THEN
    SELECT obj_description((v_sSchema || '.' || p_BASETABLENAME)::regclass, 'pg_class')
    INTO v_sComment;

    IF v_sComment IS NOT NULL THEN
      p_sCOMMENT := substr(c_sch.ExpandComment(p_sCOMMENT || ': ' || v_sComment), 1, 4000);
    END IF;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_sCOMMENT := 'Logtable for ' || p_BASETABLENAME;
END;
$$ LANGUAGE plpgsql

方案2：利用regclass自动解析Schema

如果调用存储过程时传入的表名可能带schema前缀（如otherschema.mytable），直接将输入值转为regclass类型，PostgreSQL会自动识别当前search_path中的表或带schema的表名，代码更简洁：

CREATE OR REPLACE PROCEDURE c_sch.COMPUTETABLECOMMENT (p_BASETABLENAME in VARCHAR, p_sCOMMENT in out VARCHAR)
AS $$ 
DECLARE
  v_sComment VARCHAR;
BEGIN
  p_sCOMMENT := 'Logtable for ' || p_BASETABLENAME;

  -- 自动处理带/不带schema的表名
  SELECT obj_description(p_BASETABLENAME::regclass, 'pg_class')
  INTO v_sComment;

  IF v_sComment IS NOT NULL THEN
    p_sCOMMENT := substr(c_sch.ExpandComment(p_sCOMMENT || ': ' || v_sComment), 1, 4000);
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    p_sCOMMENT := 'Logtable for ' || p_BASETABLENAME;
END;
$$ LANGUAGE plpgsql

说明：方案2更灵活，若传入无schema的表名，会根据当前会话的search_path自动查找对应表；若传入带schema的表名，则直接匹配指定schema下的表。

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