PostgreSQL/PostGIS函数执行成功但未生成目标视图排查

我编写的public.install_views函数执行成功（数据库返回ALTER FUNCTION提示，耗时42毫秒），该函数用于在指定schema中创建export_emps_v1和view_aer_infra_gc_comp两个视图，但执行后在目标schema中找不到这两个视图，无法定位问题根源。原函数代码如下：

CREATE OR REPLACE FUNCTION public.install_views(
    schema_name character varying)
    RETURNS void
    LANGUAGE '||quote||'plpgsql'||quote||'

    COST 100
    VOLATILE 

AS $BODY$
DECLARE
  schema_name text := quote_ident($1);
  version text := quote_literal('||quote||'1.0.0.1'||quote||');
  quote varchar(1);
BEGIN
  quote := '||quote||''||quote||''||quote||''||quote||';

-- Creation of the first view  
EXECUTE  
'||quote||'CREATE OR REPLACE VIEW ' || schema_name || '.export_emps_v1
 AS
 SELECT DISTINCT row_number() OVER () AS id,
    emp.id_emprise,
    pro.nom AS nom_projet,
    tel.nom_client::character varying(50) AS "CODE_TEC",
    emp.geom
   FROM ' || schema_name || '.emprise emp
     JOIN ' || schema_name || '.telecom tel ON tel.id_emprise = emp.id_emprise
     JOIN ' || schema_name || '.p_type_telecom ptl ON ptl.id_p_type_telecom = tel.id_p_type_telecom AND ptl.libelle = '||quote||'PM'||quote||'::text
     LEFT JOIN ' || schema_name || '.projet_emprise pno ON pno.id_emprise = emp.id_emprise
     JOIN ' || schema_name || '.projet pro ON pro.id_projet = pno.id_projet;
'||quote||';

-- Creation of the second view 
EXECUTE
'||quote||'
CREATE OR REPLACE VIEW ' || schema_name || '.view_aer_infra_gc_comp
 AS
 SELECT DISTINCT p.id,
    p.id_lien_infra,
    p.nom_projet,
    (string_to_array(va.commentaire, '||quote||' '||quote||'::text))[2] AS annee,
    p."CODE_POL",
    p."PROPRIO",
    p."TYPE_POS",
    p."CABLE",
    p."LONGUEUR",
    p."ETAT_AVT",
    p.geom
   FROM ' || schema_name || '.export_pol_v1 p
     LEFT JOIN ' || schema_name || '.export_ch_v1 c1 ON st_startpoint(p.geom) = c1.geom AND c1."ETAT_AVT"::text = '||quote||'A REALISER'||quote||'::text AND p."TYPE_INFRA"::text ='||quote||'AERIEN'||quote||'::text
     LEFT JOIN ' || schema_name || '.export_ch_v1 c2 ON st_endpoint(p.geom) = c2.geom AND c2."ETAT_AVT"::text = '||quote||'A REALISER'||quote||'::text AND p."TYPE_INFRA"::text ='||quote||'AERIEN'||quote||'::text
     JOIN ' || schema_name || '.vue_annotation va ON st_within(p.geom, va.geom) AND va.type = '||quote||'COMMENTAIRE BE'||quote||'::text AND (string_to_array(va.commentaire, '||quote||' '||quote||'::text))[1] = '||quote||'COMP'||quote||'::text
  WHERE (c1."ETAT_AVT"::text = '||quote||'A REALISER'||quote||'::text OR c2."ETAT_AVT"::text = '||quote||'A REALISER'||quote||'::text) AND (p."TYPE_INFRA"::text = ANY (ARRAY['||quote||'FACADE'||quote||'::character varying, '||quote||'AERIEN'||quote||'::character varying]::text[]));
'||quote||';

END;
$BODY$;

ALTER FUNCTION public.install_views(character varying)
    OWNER TO postgres;

核心问题 #

原函数的字符串拼接逻辑完全混乱：

• quote变量赋值错误，无法生成正确的单引号字符
• 外层嵌套的'||quote||'导致最终生成的EXECUTE语句存在语法错误，而PL/pgSQL默认不会主动抛出执行时的错误，所以函数返回成功，但实际视图创建语句并未正确执行。

修复后的函数代码 #

使用format()函数简化字符串拼接，避免单引号嵌套问题，同时添加异常捕获便于调试：

CREATE OR REPLACE FUNCTION public.install_views(
    p_schema_name character varying)
    RETURNS void
    LANGUAGE plpgsql
    COST 100
    VOLATILE 
AS $BODY$
DECLARE
  v_schema text := quote_ident(p_schema_name);
BEGIN
  -- 创建第一个视图
  EXECUTE format($sql$
    CREATE OR REPLACE VIEW %s.export_emps_v1
    AS
    SELECT DISTINCT row_number() OVER () AS id,
        emp.id_emprise,
        pro.nom AS nom_projet,
        tel.nom_client::character varying(50) AS "CODE_TEC",
        emp.geom
    FROM %s.emprise emp
        JOIN %s.telecom tel ON tel.id_emprise = emp.id_emprise
        JOIN %s.p_type_telecom ptl ON ptl.id_p_type_telecom = tel.id_p_type_telecom AND ptl.libelle = 'PM'::text
        LEFT JOIN %s.projet_emprise pno ON pno.id_emprise = emp.id_emprise
        JOIN %s.projet pro ON pro.id_projet = pno.id_projet;
  $sql$, v_schema, v_schema, v_schema, v_schema, v_schema, v_schema);

  -- 创建第二个视图
  EXECUTE format($sql$
    CREATE OR REPLACE VIEW %s.view_aer_infra_gc_comp
    AS
    SELECT DISTINCT p.id,
        p.id_lien_infra,
        p.nom_projet,
        (string_to_array(va.commentaire, ' '::text))[2] AS annee,
        p."CODE_POL",
        p."PROPRIO",
        p."TYPE_POS",
        p."CABLE",
        p."LONGUEUR",
        p."ETAT_AVT",
        p.geom
    FROM %s.export_pol_v1 p
        LEFT JOIN %s.export_ch_v1 c1 ON st_startpoint(p.geom) = c1.geom AND c1."ETAT_AVT"::text = 'A REALISER'::text AND p."TYPE_INFRA"::text = 'AERIEN'::text
        LEFT JOIN %s.export_ch_v1 c2 ON st_endpoint(p.geom) = c2.geom AND c2."ETAT_AVT"::text = 'A REALISER'::text AND p."TYPE_INFRA"::text = 'AERIEN'::text
        JOIN %s.vue_annotation va ON st_within(p.geom, va.geom) AND va.type = 'COMMENTAIRE BE'::text AND (string_to_array(va.commentaire, ' '::text))[1] = 'COMP'::text
    WHERE (c1."ETAT_AVT"::text = 'A REALISER'::text OR c2."ETAT_AVT"::text = 'A REALISER'::text) 
      AND (p."TYPE_INFRA"::text = ANY (ARRAY['FACADE'::character varying, 'AERIEN'::character varying]::text[]));
  $sql$, v_schema, v_schema, v_schema, v_schema, v_schema);

EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Error creating views in schema %: %', p_schema_name, SQLERRM;
    RAISE;
END;
$BODY$;

ALTER FUNCTION public.install_views(character varying)
    OWNER TO postgres;

验证步骤 #

1. 执行修复后的函数：SELECT public.install_views('你的目标schema名称');
2. 检查目标schema中的视图：

SELECT table_name 
FROM information_schema.views 
WHERE table_schema = '你的目标schema名称' 
  AND table_name IN ('export_emps_v1', 'view_aer_infra_gc_comp');

3. 若执行函数时报错，根据NOTICE中的错误信息排查：比如目标schema是否存在、依赖表是否存在、当前用户是否有创建视图的权限等。

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