What is this "ERROR: ERROR: ambiguous column reference" error?

Question

PostgreSQL

I want to fill the empty field of table 1 with the values ​​that I find by the code from table 1 in table 2

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();

error: ERROR: ERROR: ambiguous reference to column "code"
LINE 2: ...ess from zagsmarriagelist z where z.id::varchar = code)) as ...
^
DETAIL: Reference to a PL/pgSQL variable or column is implied tables.
QUERY: UPDATE zagsmarriagelist
SET bridesoate = (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) != '' 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)
WHERE zagsmarriagelist.id::varchar=code
CONTEXT: PL/pgSQL add_soate() function, line 13 , statement SQL statement

SQL state: 42702

why doesn't it recognize the 'code' variable in the subquery?

Answer 1

why doesn't it recognize the 'code' variable in the subquery?

It even recognizes, and therefore does not understand what exactly you wanted to compare here.
Look in the zagsmarriagelist table for a field called code. And so rename the variable.