Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.

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 ?

Responses

Browse pgsql-general by date

  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