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

> On 17 Jul 2021, at 0:26, David Gauthier <davegauthierpg(at)gmail(dot)com> wrote:

(…)

> 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 ?

As people advised you previously, you would probably be better off normalising your table.

For example, add a table for the links:

create table project_sibling (
project text not null
references public.projects(project)
on update cascade
on delete cascade
, sibling text not null
references public.projects(project)
on update cascade
on delete cascade
, primary key (project, sibling)
);

-- Populate it from public.projects initially
insert into project_sibling(project, sibling)
select p.project, s.sibling
from public.projects p
cross join lateral regex_split_to_table(project_csv, ',') s(sibling)
;

I had to make a few guesses there, as I don’t have your original table structure, but that’s the gist of it.

If that project_csv column gets populated by some external application, you could keep the link-table updated with insert/update/delete triggers.

Alternatively, a writable view replacing public.projects may be a possibility.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.