sql - 如何自动将 View 列映射到其原始列以插入源表？

Question

我目前正在寻找一种处理（相当简单的）PostgreSQL视图上的插入语句的通用方法，这种方法对于列名更改或添加新列将不脆弱。目标是构建不需要维护的INSTEAD触发器，并且可以轻松地将其应用于具有相似形式的新视图。

换句话说，这将是一种提供对某些视图的表式访问的公式，该视图将允许它们以表式方式进行访问。当然，这样做的目的不是要让任何人都以为某个表是愚弄人，而是为自己和将要向数据库添加记录的其他人简化事情，如果有的话很多人都会来找我它们上的任何触发代码中断。

想要这样的原因是为了处理创建记录需要已经存在的记录的情况：

CREATE TABLE bar (
        bar_id SERIAL PRIMARY KEY,
        bar_a CHAR
);

CREATE TABLE foo (
        foo_id SERIAL PRIMARY KEY,
        foo_a BIGINT,
        bar_id BIGINT NOT NULL REFERENCES bar(bar_id)
);

CREATE OR REPLACE VIEW view1 AS
        SELECT 
                foo_id, 
                bar_id, 
                foo_a AS num, 
                bar_a AS let 
        FROM (foo RIGHT JOIN bar ON foo.bar_id = bar.bar_id);

能够像这样直接插入视图中，这将是保持所有酸橙而不掉落的好方法。数据库约束对于保持系统的正常运行非常重要，但是当您运行会话并试图获取一些记录时，它们可能会阻碍您的工作，而不用花费一整天的时间。具有触发器句柄（在依赖关系的情况下）可以兼顾两者。

具体来说，这意味着可以用来运行类似

INSERT INTO bar (let) VALUES ('a');
INSERT INTO foo (num,foo_fk) VALUES (1,currval(bar_bar_id_seq)); 
-- currval only pays attention to the last insert which happens on your OWN connection, 
-- meaning it is safe to use it here

在 INSERT上查看。希望触发器不会对更改列名和修改视图敏感，这样在视图上抛出格式正确的 INSERT会成功，而无需对触发器函数进行频繁的小调整。变化。

将视图列映射到表列（主要挑战）

显然，有关此信息的信息必须以某种形式或其他形式隐藏存储，但其中最困难的部分（根据我的理解）是在无需人工干预的情况下确定视图列与源表列之间的映射。为了使 INSERT触发功能更可靠，必须从某个位置挖掘此信息。

理想情况下， view_column_usage将具有以下结构：

view_catalog|view_schema|view_name|table_catalog|table_schema|table_name|source_col|view_col|
------------+-----------+---------+-------------+------------+----------+----------+--------+
 db1        | public    | view1   | db1         | public     | bar      | bar_a    | let    |
 db1        | public    | view1   | db1         | public     | bar      | bar_id   | ----   |
 db1        | public    | view1   | db1         | public     | foo      | foo_a    | num    |
 db1        | public    | view1   | db1         | public     | foo      | foo_fk   | bar_id |
 db1        | public    | view1   | db1         | public     | foo      | foo_id   | foo_id |

但是，相反，我们得到的是：

view_catalog|view_schema|view_name|table_catalog|table_schema|table_name|column_name
------------+-----------+---------+------------+------------+-----------+-------------
db1         | public    | view1   | db1        | public     | foo       | foo_fk
db1         | public    | view1   | db1        | public     | foo       | foo_a
db1         | public    | view1   | db1        | public     | foo       | foo_id
db1         | public    | view1   | db1        | public     | bar       | bar_a
db1         | public    | view1   | db1        | public     | bar       | bar_id

这意味着我们没有开箱即用的映射，但是缺少在 pg_views.definition上为适当的视图运行解析操作的情况，如果没有框架，这是不可取的，并且可能会违反 DRY。

解决方法

硬编码

前面给出的 WITH (...) INSERT从根本上是正确的，但对于仅提供其他表中几个字段的简单标识映射的数据库视图而言，使用起来却很麻烦。 （如果视图在源字段上进行计算，确实会变得非常紧张，并且必须在将它们添加到原始列之前对其进行转换，但这不是这里的想法）。

在视图的字段中使用可预测的命名格式

我们当然可以做类似的事情

CREATE OR REPLACE VIEW view1 AS
        SELECT 
                foo_id AS foo__foo_id, 
                bar_id AS bar__bar_id, 
                foo_a AS foo__foo_a, 
                bar_a AS bar__bar_a 
        FROM (foo RIGHT JOIN bar ON foo.bar_id = bar.bar_id);

然后让函数使用这种格式来确定所有内容的放置位置...

但是，除了降低视图的易读性之外，这与将 INSERT操作硬编码到触发器中同样会很脆弱，因为如果视图的列名设置错误，该函数就会阻塞。我们可以提出一种解决易读性问题的方法，但这实际上会使脆弱性部分更糟，这实际上是主要问题。

只是处理一些脆弱的触发器并继续前进

即使我是在这种级别上处理db-structure的主要人员，但对数据库如此接近我还是陌生的。因此，不灵活的 INSTEAD触发器可能只是相当标准的。如果可能的话，我仍然希望有一个更好的解决方案，以便当我不在办公室且其他开发人员仍在工作时，以及在我转到其他职位时，一切都可以顺利进行。

笔记

编辑：正如 Patrick正确指出的那样，鉴于 INSTEAD在每一行上运行，上述触发功能会对性能产生影响（除非我只是缓存映射并适当地更新它，以自己的方式是混乱的）。我要澄清的是，由于事物处于不断变化的状态，因此该触发器主要是出于开发目的的便利，而这将使部署变得更简单，更快捷。如果有人有办法（但是很慢），这只是为了学习Postgre的目的而放在我的口袋里将是一件有用的事情。

特别是对于更深层次的现实情况，尝试智能地计算函数中各个源表的依赖顺序可能是愚蠢的。既然这应该不会有太大变化，所以我不介意为触发器函数提供一些参数来告诉它哪些表应该首先获取新行。

随着事情变得更加复杂，如果在视图中同时显示3或4个关系，则可以向触发器提供一些有关如何将关系合并在一起的信息也可能会很好。该函数不需要太灵活以容忍表名或主键列名的更改（无需对其代码进行调整）。我们不是在这里建立天网。无论如何，鉴于上述较简单的测试用例，您不必为此担心。我会找出丑陋的东西。

同样，假设外键（例如像 something_fk这样的东西始终转换为 something.something_id之类的东西）的一致性级别是很好的，因为无论如何，这种事情在整个数据库中应该是规则的。

如前所述，我们将假定与 INSERT触发器一起使用的视图将不会在源列上执行计算（否则，为了保持直观性，必须在插入值之前找到这些操作的逆函数）。我们不要去那里。

潜在有用的资源

我发现 pg_attribute是一个系统表，其中可能包含一些有用的内容，其中包括有关表列的出现顺序的信息。 information_schema.columns也可以与 view_column_usage一起提供某些东西（如上所述）。

版

最后，我目前正在使用PostgreSQL 9.2，但是如果它允许使用更干净的解决方案，我将愿意进行升级。

Answer 1

对此没有简单的解决方案，也没有解决方案。
您在此处提出的内容意味着您将具有一个触发函数，该函数足够通用，可以动态适应不断变化的视图定义以及基础表中的更改。出于多种原因，这是一个坏主意，我仅举两个理由：

如果您的开发人员精通（特权），可以修改表和视图定义，那么他们也应该知道如何为视图编写相应的INSTEAD OF触发器。知道DDL但不知道PL / pgSQL的开发人员应发送回PG school。
由于必须查找列名，源表，将视图列映射到表列，将值映射到列，处理开发人员提出的约束，因此如您描述的那样灵活的触发函数将相当复杂。列级或表特权等，等等。因此将很慢。由于INSTEAD OF触发函数始终执行FOR EACH ROW，因此它们应精简且快速；您正在为严重的性能瓶颈做好准备。

您的表和视图定义多久更改一次？只需在您的商店中制定一条业务规则，即除非有许多人讨论并同意更改（包括触发器的更改），否则表格和视图不会更改。测试任何建议的更改。记录下来。再测试一下。最后，在推出应用程序之前进行更多测试。
我信任的不是一家商店，那里的开发人员对PostgreSQL的了解显然有限，他们无法对可能破坏应用程序的数据模型进行更改。
改进您的开发人员和您的开发过程，而不是PostgreSQL。
你可以做什么
也就是说，仔细设计表和触发器函数可以大大减少触发器函数的数量。基本规则是保持触发器较小且快速，使触发器仅执行明确定义的事情，例如将INSERT传播到视图的基础表。
INSTEAD OF触发函数基本上只不过是将在视图上运行的命令转换为在基础表上运行的一个或多个命令。一个视图上的INSTEAD OF INSERT触发功能与另一视图上的#view#触发功能非常相似，因此您可以尝试使用模板的方法。模板可能如下所示：

CREATE FUNCTION trf_ioi_#view# () RETURNS trigger AS $$
BEGIN
  -- Put in INSERT statements on all the tables making up the view
  -- Example: Simple insert:
  -- INSERT INTO table (columns...)
  -- VALUES (NEW...);

  -- Get serial PK from 1 table to insert in other tables
  -- INSERT INTO table_PK (columns...)
  -- VALUES (NEW...)
  -- RETURNING pk INTO tid; (DECLARE tid before BEGIN)

  -- INSERT INTO table_FK (FK_column, other columns...)
  -- VALUES (tid, NEW...);

  RETURN NEW; -- to make trigger proceed; NULL to make trigger fail
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_ioi_#view#
INSTEAD OF INSERT ON #view#
FOR EACH ROW EXECUTE PROCEDURE trf_ioi_#view#;

将 替换为视图的实际名称，您便拥有了开发人员可以轻松完成的样板代码。创建这样的触发函数并不是真的很困难，这只是开发人员必须学习的新技巧。通常，顺便说一句，它们将与现有的触发器函数一起使用，因此它们仅需更新函数主体中的语句即可反映视图或基础表中的更改。给他们两天时间来使用PL / pgSQL和触发函数，他们应该会顺利进行。