PostgreSQL存储过程bulletin_abbrege报num列不存在问题咨询

错误原因 #

PostgreSQL的视图定义是静态解析的SQL对象，执行CREATE OR REPLACE VIEW语句时，SQL解析器只会在当前涉及的表字段列表中查找标识符num，不会向上识别PL/pgSQL存储过程的入参/局部变量，因此会把入参num识别为不存在的表字段，抛出42703错误。

解决方案 #

根据你的业务需求可选择以下两种方案：

方案1：需要动态根据入参生成过滤后的持久化视图 #

使用PL/pgSQL的动态SQL语法EXECUTE拼接视图定义语句，将入参值拼入语句中，示例代码如下：

CREATE OR REPLACE PROCEDURE bulletin_abbrege(in num INTEGER)
language plpgsql
AS $$
BEGIN
  EXECUTE format(
    'CREATE OR REPLACE VIEW bulletin_abrege AS
    SELECT concat(substring(candidat_nom,1,1),substring(candidat_prenom,1,1)) as initiales,parti_abv as abbrevation_parti,compte_nom as compte_nom
    FROM candidat INNER JOIN parti ON candidat.parti_id=parti.parti_id INNER JOIN compte ON candidat.compte_id=compte.compte_id
    WHERE candidat.compte_id = %L', num
  );
END;
$$;

上述代码中format函数的%L占位符会自动转义参数值，可避免SQL注入风险。

方案2：仅需要查询对应结果，不需要持久化视图 #

如果你的需求只是根据入参查询对应数据，无需创建视图，直接用返回结果集的函数实现更合理，示例代码如下：

CREATE OR REPLACE FUNCTION get_bulletin_abbrege(p_num INTEGER)
RETURNS TABLE (
  initiales TEXT,
  abbrevation_parti TEXT,
  compte_nom TEXT
)
language plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT concat(substring(candidat_nom,1,1),substring(candidat_prenom,1,1)) as initiales,parti_abv as abbrevation_parti,compte_nom as compte_nom
  FROM candidat INNER JOIN parti ON candidat.parti_id=parti.parti_id INNER JOIN compte ON candidat.compte_id=compte.compte_id
  WHERE candidat.compte_id = p_num;
END;
$$;

调用方式为SELECT * FROM get_bulletin_abbrege(你的参数值);，不需要额外创建视图对象。

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