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

On Fri, Jul 16, 2021 at 3:26 PM David Gauthier <davegauthierpg(at)gmail(dot)com>
wrote:

> This stored procedure ...
>

> create or replace function validate_proj_csv (proj_csv varchar)
>

It is a function - I don't think you can used stored procedures in check
constraints...

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

I'm actually surprised this alter command worked at all since you are
violating a requirement for check constraints - namely that the expression
be immutable. Your function, regardless of its declaration (which is
default volatile), is not immutable.

How to get this to work ?
>

You really need to re-write this as a trigger function.

David J.