Postgres Pro
Downloads
Home   >   mailing lists

Re: Need to check each element of an array satisfies a foreign key constraint - Mailing list pgsql-general

From David Gauthier
Subject Re: Need to check each element of an array satisfies a foreign key constraint
Date
Msg-id CAMBRECDFpwpK0nHn-5XuO6pjB0cfieSryaBHMLtqPKQV-4Qhhg@mail.gmail.com
Whole thread Raw
In response to Re: Need to check each element of an array satisfies a foreign key constraint  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: Need to check each element of an array satisfies a foreign key constraint  (Ken Tanzer <ken.tanzer@gmail.com>)
List pgsql-general
Tree view
Ok, thanks. 

I was looking for, but didn't find, something like... each_element_of(regexp_split_to_array(children_csv)) references projects(project);
Of course the "each_element_of" is my creation here :-)

On Tue, Jul 13, 2021 at 10:07 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 13, 2021, David Gauthier <davegauthierpg@gmail.com> wrote:

I suppose I could write a stored procedure to do this and call it in a check constraint. But I was wondering if there is something more elegant.


You cannot use a check constraint here as the behavior is not immutable.  You can use a trigger function though.  Or normalize the table and use the built-it foreign key triggers.

David J.
 

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Need to check each element of an array satisfies a foreign key constraint
Next
From: Rich Shepard
Date:
Subject: psycopg2 mail list subscription issues