хочу заполнить пустое поле таблицы 1 значениями, которые нахожу по коду из таблицы 1, в таблице 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: ОШИБКА: неоднозначная ссылка на столбец "code"
LINE 2: ...ess from zagsmarriagelist z where z.id::varchar = code)) as ...
^
DETAIL: Подразумевается ссылка на переменную PL/pgSQL или столбец таблицы.
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(), строка 13, оператор SQL-оператор
SQL state: 42702
почему он не опознает переменную 'code' в подзапросе?