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

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 <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> 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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com