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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.
Дата
Msg-id CAKFQuwYpL=AMnnDjGU1daByePBdFMiTcWCE9j1e6OAOUF_8+Sg@mail.gmail.com
обсуждение исходный текст
Ответ на 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>)
Список pgsql-general
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Peter Geoghegan
Дата:
Сообщение: Re: Frequetly updated partial index leads to bloat on index for Postresql 11
Следующее
От: Alban Hertroys
Дата:
Сообщение: Re: Getting "ERROR: upper bound of FOR loop cannot be null" when trying to use a stored proc in a check constraint def.