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;
$$ ;
... 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)
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
What's going on ?
How to get this to work ?
