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

Hi:

I have a column in a table which is a csv of values and I need to make sure each element of the csv = the PK of that same table.

create table projects (

  project varchar primary key,

  children_csv varchar );

insert into projects (project,children_csv) values 

('prj1',null),

('prj2',null),

('prj3','prj1,prj2');

I need to make sure that the elements of 'prj1,prj2' are both valid projects.

I'm thinking the csv should be split into an array (regexp_split_to_array) but the constraint needs to somehow iterate over each element to check that they are all valid.  

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.

Thanks in Advance !