postgresql - 使用表 fieldname:possible_values 验证是否有其他表值并填充字段

Question

我有一组表，其中包含大部分字段的可能值。我想在每个表中创建一个字段，其中包含包含不接受值的列的列名。

下面是一个与我想做的相对应的试验场。

预期结果是：

Table foo
a  | b | c      | check
a  | 1 | foobar |
b  | 1 | foobaz |
ab | 1 | foofoo | a c

Table bar
a  | x      | y  | check
a  | foobar | 3  |
b  | foobaz | 2  |
ab | foofoo | 2  | x

预定义的检查看起来像这样

table_name | field | permittedvalue
foo        |a      |a
foo        |a      |b
foo        |b      |NC
foo        |c      |foobar    
foo        |c      |foobaz 
bar        |a      |a
bar        |a      |b
bar        |a      |ab
bar        |b      |foo
bar        |b      |bar
bar        |c      |NC       

我需要它在一个以 table_name 作为参数的函数中。

create table foo (
a character varying,
b int,
c character varying);

insert into foo values('a',1,'foobar');
insert into foo values('b',1,'foobaz');
insert into foo values('ab',2,'foofoo'); -- Not an accepted value in table check_foo

create table bar (
a character varying,
x character varying,
y int);

insert into bar values('a','foo',3);
insert into bar values('b','bar',2);
insert into bar values('ab','foobar',2);-- Not an accepted value in table check_foo

create table check_foo(tablename character varying,
                      field character varying,
                      permittedvalue character varying);

insert into check_foo values('foo','a','a');
insert into check_foo values('foo','a','b');
insert into check_foo values('foo','b','NC'); -- 'NC' means the column is not concerned so must not be validated
insert into check_foo values('foo','c','foobar');    
insert into check_foo values('foo','c','foobaz');  
insert into check_foo values('bar','a','a');
insert into check_foo values('bar','a','b');
insert into check_foo values('bar','a','ab');
insert into check_foo values('bar','b','foo');
insert into check_foo values('bar','b','bar');
insert into check_foo values('bar','c','NC');

标签： postgresql