> On 17 Jul 2021, at 0:26, David Gauthier <davegauthie...@gmail.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.



Reply via email to