database - postgres 9.3+ : constraint to ensure table and column exist in database

Question

如何确保新记录包含引用数据库中当前存在的架构、表和列的值？

例如，给定一个表:

CREATE TEMP TABLE "column_reference" (
      "gid"    SERIAL PRIMARY KEY
    , "val"    INTEGER
    , "schema" TEXT
    , "table"  TEXT
    , "column" TEXT
);

如何确保 schema.table.column 存在？

我尝试了 information_schema.columns 的 fkey，但是，当然， View 的外键是不允许的。

从 columns View 定义中也可以看出，我需要多个表才能获取架构、表和列名称，因此我无法为源表创建单个外键。

我目前的解决方法是从 information_schema.columns View 手动创建一个 __columns 表并引用它。考虑到我此时恰好对该项目拥有控制权，此方法可行，但我正在寻找一个永久的、动态的解决方案。

我可以使用不同的约束或方法吗？

Answer 1

您可以创建一个触发器函数来检查您想要的内容，并将此函数与在表的插入或更新之前触发的触发器相关联:

这可能是你的触发函数:

CREATE FUNCTION column_reference_check()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS 
$BODY$
begin
    /* Check for the existence of the required column */
    if EXISTS (
          SELECT * 
          FROM information_schema.columns 
          WHERE
                  table_schema = new.schema
              AND table_name   = new.table
              AND column_name  = new.column )
    then
        /* Everything Ok */
        return new ;
    else
        /* This is approx. what would happen if you had a constraint */
        RAISE EXCEPTION 'Trying to insert non-matching (%, %, %)', new.schema, new.table, new.column ;

        /* As an alternative, you could also just return NULL
           As a result, the row is *not* inserted, but execution continues */
        return NULL ;
    end if ;
 end ;
$BODY$;

要将此函数与触发器相关联，您可以使用:

CREATE TRIGGER column_reference_check_trg
    BEFORE INSERT OR UPDATE OF "schema", "table", "column"
    ON column_reference
    FOR EACH ROW
    EXECUTE PROCEDURE column_reference_check();

现在您可以尝试执行以下INSERT，应该会成功:

INSERT INTO column_reference 
   VALUES (2, 1, 'pg_catalog', 'pg_statistic', 'starelid');

但是如果你尝试这个:

INSERT INTO column_reference 
   VALUES (-1, 1, 'false_schema', 'false_table', 'false_column');

...你得到一个异常(exception):

ERROR:  Trying to insert non-matching (false_schema, false_table, false_column)
CONTEXT:  PL/pgSQL function column_reference_check() line 16 at RAISE