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

[Alban Hertroys]

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

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

[Adrian Klaver]

On 7/17/21 10:13 AM, David Gauthier wrote:
Please reply to list also.
Ccing list.


That was it.  I added...

   if (proj_csv is null) then
     return(1);
   end if;

... and it works fine.

Back in earlier versions of PG (I'm talking v8), existing column values 
weren't checked.  I remember you had to manually run a query using your 
stored procedure to see if there would be any existing violations before 
creating the constraint.  The way it is now is an improvement, much better.


Glad it worked. Just be aware that using a function as a check 
constraint is skirting the rules for CHECK as explained here:


https://www.postgresql.org/docs/current/sql-createtable.html

There have been multiple posts to --general where that has come back to 
bite someone. Generally because the function reaches out to some other 
object in the database, which may or may not be there later. To me your 
particular function looks fairly benign, still you are standing close to 
the edge:)




Thanks Adrian.

On Fri, Jul 16, 2021 at 6:30 PM Adrian Klaver > wrote:


On 7/16/21 3:26 PM, David Gauthier wrote:
 > This stored procedure ...

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

I'm going to say you have a NULL value in sibling_project_csv in the
table.

 > How to get this to work ?
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com