From: | David Gauthier <davegauthierpg(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def. |
Date: | 2021-07-16 22:26:17 |
Message-ID: | CAMBRECB+c1nA-AGx8JMX-4RfVxyKLczJt6gkiPK0=AKu26h-uQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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;
$$ ;
... 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 ?
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2021-07-16 22:30:51 | Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def. |
Previous Message | Laurenz Albe | 2021-07-16 17:00:23 | Re: dealing with dependencies |