Postgres Pro
Downloads
Home   >   mailing lists

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

From David G. Johnston
Subject Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.
Date
Msg-id CAKFQuwYpL=AMnnDjGU1daByePBdFMiTcWCE9j1e6OAOUF_8+Sg@mail.gmail.com
Whole thread Raw
In response to Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.  (David Gauthier <davegauthierpg@gmail.com>)
List pgsql-general
Tree view
On Fri, Jul 16, 2021 at 3:26 PM David Gauthier <davegauthierpg@gmail.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.

pgsql-general by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: Frequetly updated partial index leads to bloat on index for Postresql 11
Next
From: Alban Hertroys
Date:
Subject: Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.