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

Question

I want to fill an empty field of table 1 with 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();

displays an error:

ERROR: ERROR: ambiguous reference to column "code" LINE 2: ... ess from zagsmarriagelist z where z.id::varchar = code)) as ... ^ DETAIL: Assumes a reference to a PL / pgSQL variable or table column. 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

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

Unrelated to the problem at hand, but I think you can get rid of the whole looping and over complicated sub-query, and write that with a single UPDATE statement. Something like this: pastebin.com/5v6xBW8R — a_horse_with_no_name
I am not sure what your intention behind the regexp_replace is, but I think it comes from a misunderstanding on what r contains. You probably just want: code := r.id; — a_horse_with_no_name

Bohemian Bohemian · Accepted Answer · 2020-09-03T07:50:13

The problem is here:

where id::varchar = code

because ate_history has a column called code and you defined a variable code, so it's ambiguous as to which one the expression refers to, as both are in scope.

Normally you would just must qualify it, but you can't so just rename it uniquely.

declare _code

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

where id::varchar = _code

or if you wanted the column from ate_history:

where id::varchar = a.code

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

1 Answers