Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.
От | David Gauthier |
---|---|
Тема | Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def. |
Дата | |
Msg-id | CAMBRECB+c1nA-AGx8JMX-4RfVxyKLczJt6gkiPK0=AKu26h-uQ@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.
Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def. Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def. |
Список | pgsql-general |
This stored procedure ...
create or replace function validate_proj_csv (proj_csv varchar)
returns int
language plpgsql
as
$$
-- This function used in a check constraint in the public.projects table to ensure that
-- all projects in column sibling_project_csv are valid projects.
DECLARE
proj_arr varchar[];
see_prj int;
BEGIN
proj_arr := regexp_split_to_array(proj_csv,',');
for x in 1 .. array_upper(proj_arr,1)
loop
select 1 into see_prj from public.projects where project = proj_arr[x];
if (see_prj is null) then
raise notice 'Project "%" in project csv "%" is not a valid project.', proj_arr[x],proj_csv;
return 0;
end if;
end loop;
return 1;
END;
$$ ;
create or replace function validate_proj_csv (proj_csv varchar)
returns int
language plpgsql
as
$$
-- This function used in a check constraint in the public.projects table to ensure that
-- all projects in column sibling_project_csv are valid projects.
DECLARE
proj_arr varchar[];
see_prj int;
BEGIN
proj_arr := regexp_split_to_array(proj_csv,',');
for x in 1 .. array_upper(proj_arr,1)
loop
select 1 into see_prj from public.projects where project = proj_arr[x];
if (see_prj is null) then
raise notice 'Project "%" in project csv "%" is not a valid project.', proj_arr[x],proj_csv;
return 0;
end if;
end loop;
return 1;
END;
$$ ;
... works fine...
dvdb=# select validate_proj_csv('sabin,strix2,ipu1.0');
validate_proj_csv
-------------------
1
(1 row)
dvdb=# select validate_proj_csv('sabin,strix2,ipu1.00');
NOTICE: Project "ipu1.00" in project csv "sabin,strix2,ipu1.00" is not a valid project.
validate_proj_csv
-------------------
0
(1 row)
validate_proj_csv
-------------------
1
(1 row)
dvdb=# select validate_proj_csv('sabin,strix2,ipu1.00');
NOTICE: Project "ipu1.00" in project csv "sabin,strix2,ipu1.00" is not a valid project.
validate_proj_csv
-------------------
0
(1 row)
But when I try to use it in a check constraint....
dvdb=# alter table projects add constraint validate_sibling_project_csv check (validate_proj_csv(sibling_project_csv) = 0);
ERROR: upper bound of FOR loop cannot be null
CONTEXT: PL/pgSQL function validate_proj_csv(character varying) line 14 at FOR with integer loop variable
dvdb=# alter table projects add constraint validate_sibling_project_csv check (validate_proj_csv(sibling_project_csv) = 0);
ERROR: upper bound of FOR loop cannot be null
CONTEXT: PL/pgSQL function validate_proj_csv(character varying) line 14 at FOR with integer loop variable
What's going on ?
How to get this to work ?
В списке pgsql-general по дате отправления: