时间戳

首页 > 解决方案 > 这是什么“错误：不明确的列引用错误”？

postgresql - 这是什么“错误：不明确的列引用错误”？

问题描述

我想用表1中的代码在表2中找到的值填充表1的空字段

    CREATE OR REPLACE FUNCTION public.add_soate(
 )
    RETURNS SETOF zagsmarriagelist 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
    
AS $BODY$
DECLARE
    r zagsmarriagelist%rowtype;
DECLARE code varchar;
BEGIN

  FOR r IN
   SELECT id FROM zagsmarriagelist
  LOOP
    code := (select nullif(regexp_replace(r::varchar, '\D','','g'), ''));
   UPDATE zagsmarriagelist
   SET bridesoate = (select substring(a.code from 1 for 14) from ate_history a where a.ate::varchar=(select bridebirthaddress from zagsmarriagelist where id::varchar=code))
           WHERE id::varchar=code;
   RETURN NEXT r;
 END LOOP;
 RETURN;
END
$BODY$;

ALTER FUNCTION public.add_soate()
    OWNER TO postgres;
  
select * from add_soate();

显示错误：

错误：错误：对列“代码”的模糊引用第 2 行：... ess from zagsmarriagelist z where z.id::varchar = code)) as ... ^ 详细信息：假设引用 PL / pgSQL 变量或表柱子。QUERY: UPDATE zagsmarriagelist SET Brisoate = (case when (select z.bridebirthaddress from zagsmarriagelist z where z.id::varchar = code)! = '' Then cast ((select substring (a.code from 1 for 14) from ate_history a where a.ate :: varchar = (select z.bridebirthaddress from zagsmarriagelist z where z.id::varchar = code)) as integer) else NULL END),groomsoate = (case when (select z.groombirthaddress from zagsmarriagelist z where z .id::varchar = code)! = '' 然后从 ate_history a where a.ate: 中强制转换 ((select substring (a.code from 1 for 14) where a.ate: : varchar = (select z.bridebirthaddress from zagsmarriagelist z where z.id ::

为什么它不能识别子查询中的变量“代码”？

标签： postgresql

解决方案

问题在这里：

where id::varchar = code

因为ate_history有一个名为的列code 并且您定义了一个 variable code，所以对于表达式所指的那个是模棱两可的，因为两者都在范围内。

通常你只需要限定它，但你不能只重命名它。

declare _code

_code := (select nullif(regexp_replace(r::varchar, '\D','','g'), ''));

where id::varchar = _code

或者，如果您想要以下列ate_history：

where id::varchar = a.code

推荐阅读