当从另一个表中删除时，如何使 PostgreSQL 在表中插入一行？

Question

提问by Benson Jin

We have an application, which will delete a row from a table based on user requests. I cannot change the application code. However, I want to insert a row into another table (kinda like a journal log) with information from a few other tables based on information of the row that is being deleted.

我们有一个应用程序，它将根据用户请求从表中删除一行。我无法更改应用程序代码。但是，我想根据要删除的行的信息将一行插入另一个表（有点像日志日志），其中包含来自其他几个表的信息。

How do I achieve this within PostgreSQL?

我如何在 PostgreSQL 中实现这一点？

Answer 1

回答by Erwin Brandstetter

Write a trigger function. Something like this:

写一个触发函数。像这样的东西：

CREATE OR REPLACE FUNCTION trg_backup_row()
  RETURNS trigger AS
$BODY$
BEGIN

INSERT INTO other_tbl
SELECT (OLD).*, t.other_col                -- all columns of from old table
-- SELECT OLD.col1, OLD.col2, t.other_col  -- alternative: some cols from old tbl
FROM   third_tbl t
WHERE  t.col = OLD.col  -- link to third table with info from deleted row
AND    <unique_condition_to_avoid_multiple_rows_if_needed>;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

And a trigger ON DELETE. Like this:

和一个触发器ON DELETE。像这样：

CREATE TRIGGER delaft
  AFTER DELETE
  ON tbl
  FOR EACH ROW
  EXECUTE PROCEDURE trg_backup_row();

Key elements

关键要素

Best make it a trigger AFTER DELETEand FOR EACH ROW.
To return all columns from the old table use the syntax (OLD).*. See the manual about accessing composite types. Alternatively OLD.*is valid syntax, too, because OLDis added to the FROMclause implicitly. For a VALUESexpression it would have to be (OLD).*, though. Like:
```
INSERT INTO other_tbl
VALUES((OLD).*, some_variable)
```
You can include values from any other table like I demonstrate. Just make sure to get a single row, or you create multiple entries.
As the trigger fires AFTERthe event, the function can RETURN NULL.

最好让它成为触发器AFTER DELETE和FOR EACH ROW。
要返回旧表中的所有列，请使用语法(OLD).*。请参阅有关访问复合类型的手册。或者OLD.*也是有效的语法，因为OLD它FROM隐式添加到子句中。但是，对于VALUES表达式，它必须是(OLD).*。喜欢：
```
INSERT INTO other_tbl
VALUES((OLD).*, some_variable)
```
您可以像我演示的那样包含任何其他表中的值。只需确保获得一行，或者您创建多个条目。
当触发器触发AFTER事件时，函数可以RETURN NULL。

About visibility

关于可见性

In response to @couling's watchful comment.

回应@couling 的谨慎评论。

While foreign keys can be declared as DEFERRED, this will only defer the integrity check, not the deletion itself. Rows that are deleted in triggers executed before the one at hand orby ON DELETE CASCADEforeign keys will not be visible any moreat the time this AFTER DELETEtrigger is called. (It all happens in one transaction obviously. None of these details matter for other transactions, which will see all or none of the effects. Refer to the manual for more about the MVCC model and transaction isolation.)

虽然可以将外键声明为DEFERRED，但这只会推迟完整性检查，而不是删除本身。那些在一个手头之前触发器执行删除的行或由ON DELETE CASCADE外键将不可见任何更在这个时间AFTER DELETE触发器被调用。（显然，这一切都发生在一个事务中。这些细节对其他事务都不重要，其他事务将看到全部或全部影响。有关MVCC 模型和事务隔离的更多信息，请参阅手册。）

Therefore, if you want to include values from rows depending in such a way in your INSERT, be sure to call this trigger beforethose rows get deleted.

因此，如果您想在您的中以这种方式包含来自行的值，请INSERT确保在删除这些行之前调用此触发器。

You may have to you make this trigger BEFORE DELETE.

你可能不得不让这个触发器BEFORE DELETE。

Or it can mean that you have to order your triggers accordingly, BEFOREtriggers come before AFTERtriggers, obviously. And triggers at the same level are executed in alphabetical order.

或者这可能意味着您必须相应地对触发器进行排序，显然，BEFORE触发器先于AFTER触发器。并且同一级别的触发器按字母顺序执行。

However, as long as I am super precise here, I might also add that changes made to the row (or depending rows) in other BEFOREtriggers are also only visible if those are called beforethis one.

但是，只要我在这里非常精确，我还可以补充一点，对其他BEFORE触发器中的行（或依赖行）所做的更改也只有在这些触发器之前调用时才可见。

My advice to make it an AFTERtrigger was because it is less prone to complications and cheaper if other trigger might cancel (roll back) the DELETEhalf way through the operation - as long as none of the above applies.

我将其设为AFTER触发器的建议是因为如果其他触发器可能会DELETE在操作的中途取消（回滚），那么它不太容易出现并发症并且更便宜- 只要上述条件都不适用。

Answer 2

回答by Borys

You may want to use functions that I wrote for keeping historical data. Short decription:

您可能想使用我编写的用于保存历史数据的函数。简短说明：

Historical data are kept in separate schema named audit. So first step would be creating this schema:

历史数据保存在名为audit 的单独模式中。所以第一步是创建这个架构：

CREATE SCHEMA audit;

In audit schema one can find exact copy of tables from public, that are created dynamically when first change in data in public schema occurs. So, before first use of database audit schema remains empty until user will do his first insert into one of the tables.

在审计模式中，可以从 public 中找到表的精确副本，这些副本是在 public 模式中的数据发生第一次更改时动态创建的。因此，在第一次使用数据库审计模式之前，审计模式保持为空，直到用户第一次插入其中一个表。

Function _audit_table_creator(name) is copying then structure of the table from public schema and creates the same table in audit schema with some additional columns, that I called ‘audit stamp'. Audit stamp keeps the informations about:

函数 _audit_table_creator(name) 正在从公共模式复制表的结构，并在审计模式中创建相同的表，其中包含一些附加列，我称之为“审计标记”。审计印章保留有关以下方面的信息：

time when record was deleted (shift_time),
user that made the deletion (who_altered),
‘DELETE' stamp (alter_type), and
column that has been changed - for update operations only (changed_columns);

记录被删除的时间（shift_time），
进行删除的用户 (who_altered)，
“删除”戳（alter_type），和
已更改的列 - 仅用于更新操作 (changed_columns)；

I think the biggest advantage of this solution is that composite primary keys are supported(function _where_clause_creator(text[]) creates proper where clause for table called by trigger by concatenating strings in the right order);

我认为该解决方案的最大优点是支持复合主键（函数 _where_clause_creator(text[]) 通过按正确顺序连接字符串为触发器调用的表创建适当的 where 子句）；

Viewing historical records:

查看历史记录：

Everytime we want to retrieve archival data, we have to use aliases, i.e. to retrieve historical data about user whos user_id = 5 one have to write:

每次我们要检索档案数据时，我们都必须使用别名，即检索有关 user_id = 5 的用户的历史数据，必须这样写：

SELECT * FROM audit.users WHERE user_id = 5;

So the same queries can be used in both schemas but to retrieve historical data one have to add ‘audit.' before table name.

因此，可以在两种模式中使用相同的查询，但要检索历史数据，必须添加“审计”。在表名之前。

You may want to create delete triggers automatically for all tables in database at once, if you do you can just do the query:

您可能希望一次为数据库中的所有表自动创建删除触发器，如果您这样做，您可以执行以下查询：

SELECT * FROM  audit_gen_triggers();

The main function:

主要功能：

CREATE OR REPLACE FUNCTION audit_delete() 
  RETURNS trigger AS
$BODY$DECLARE
t_name text;                 
query_op text;              
primary_keys text;           
c record;
key_arr text;
keys_arr text;
p_r text;

    BEGIN

    t_name := 'audit.' || TG_TABLE_NAME;
         IF NOT EXISTS(SELECT 1 FROM pg_tables WHERE schemaname = 'audit' AND 
                       tablename = TG_TABLE_NAME) THEN
           EXECUTE 'SELECT _audit_table_creator(table_name := ()::name)' 
           USING TG_TABLE_NAME;
        END IF; 

        FOR c IN SELECT pg_attribute.attname
                      FROM pg_index, pg_class, pg_attribute 
                      WHERE 
                      pg_class.oid = TG_TABLE_NAME::regclass AND
                      indrelid = pg_class.oid AND
                      pg_attribute.attrelid = pg_class.oid AND
                      pg_attribute.attnum = ANY(pg_index.indkey) AND 
                      indisprimary LOOP

               key_arr := c.attname || ', ().' || c.attname;
               keys_arr := concat_ws(',', keys_arr, key_arr);
               END LOOP;
               keys_arr := '{' || keys_arr || '}';

        EXECUTE 'SELECT _where_clause_creator(VARIADIC ()::text[])' 
        INTO p_r USING keys_arr;
        -- raise notice 'tablica where: %', p_r;

        -- zapisz do tabeli audytowanej wszystkie usuniete wartosci
        query_op := 'INSERT INTO '||  t_name ||
                           ' SELECT NEXTVAL(''serial_audit_' 
                             ||  TG_TABLE_NAME ||'''::regclass),
                             CURRENT_USER, ''' ||  TG_OP || ''',
                             NULL,
                             NOW(),
                             ().*
                             FROM ' || TG_TABLE_NAME ||
                             ' WHERE  ' || p_r;
                EXECUTE query_op USING OLD;
        RETURN OLD;
    END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

trigger:

扳机：

CREATE TRIGGER table_name_delete_audit
  BEFORE DELETE
  ON table_name
  FOR EACH ROW
  EXECUTE PROCEDURE audit_delete();

other functions used:

使用的其他功能：

    CREATE OR REPLACE FUNCTION _array_position(anyarray, anyelement)
      RETURNS integer AS
    $BODY$
    SELECT i
       FROM (SELECT generate_subscripts(, 1) as i, unnest() as v) s
      WHERE v = 
      UNION ALL
      SELECT 0 
      LIMIT 1;
    $BODY$
      LANGUAGE sql STABLE
      COST 100;


CREATE OR REPLACE FUNCTION _audit_table_creator(table_name name)
RETURNS void AS
$BODY$
DECLARE
query_create text; 

BEGIN
query_create := 'DROP TABLE IF EXISTS temp_insert; 
                 DROP TABLE IF EXISTS temp_insert_prepared';
EXECUTE query_create;

query_create := 'DROP SEQUENCE IF EXISTS serial_audit_' ||  table_name;
                 EXECUTE query_create;
query_create := 'CREATE SEQUENCE serial_audit_' || table_name || ' START 1; 
                 ALTER TABLE serial_audit_' || table_name || 
                 ' OWNER TO audit_owner;';
                 EXECUTE query_create;
query_create := 'CREATE TEMPORARY TABLE temp_insert_prepared ( '
                 || table_name || '_audit_id bigint DEFAULT 
                 nextval(''serial_audit_' || table_name || '''::regclass),
                 who_altered text DEFAULT CURRENT_USER,
                 alter_type varchar(6) DEFAULT ''INSERT'',
                 changed_columns text,
                 shift_time timestamp(0) without time zone DEFAULT NOW(),
                 PRIMARY KEY(' || table_name || '_audit_id )) ON COMMIT DROP';

EXECUTE query_create;

query_create := 'CREATE TEMPORARY TABLE temp_insert ON COMMIT DROP AS TABLE 
                ' || table_name;
                EXECUTE query_create;

                query_create := 'CREATE TABLE audit.' || table_name || 
                ' AS SELECT a.*, b.* FROM temp_insert_prepared a, temp_insert b 
                 WITH NO DATA';
                EXECUTE query_create;

    END;
    $BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;

CREATE OR REPLACE FUNCTION _where_clause_creator(VARIADIC keys_given text[])
RETURNS text AS
$BODY$
DECLARE
x text;
where_clause text;

BEGIN
FOREACH x IN ARRAY keys_given LOOP
IF ((SELECT _array_position(keys_given, x))%2) <> 0 THEN
where_clause := concat_ws(' AND ', where_clause, x);
ELSE
       where_clause := concat_ws(' = ', where_clause, x);
END IF;
END LOOP;
RETURN where_clause;
END;
$BODY$
  LANGUAGE plpgsql STABLE
  COST 100;



CREATE OR REPLACE FUNCTION audit_gen_triggers()
  RETURNS void AS
$BODY$
DECLARE
r record;
query_create text;
BEGIN
FOR r IN SELECT table_name
             FROM information_schema.tables
             WHERE table_schema = current_schema AND
                         table_type = 'BASE TABLE' LOOP

query_create := 'DROP TRIGGER IF EXISTS ' || r.table_name || '_delete_audit ON ' 
                 || r.table_name || ' CASCADE;
                 CREATE TRIGGER ' || r.table_name || '_delete_audit
                 BEFORE DELETE
                 ON ' || r.table_name || '
                 FOR EACH ROW
                 EXECUTE PROCEDURE audit_delete();';

EXECUTE query_create;
END LOOP;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

当从另一个表中删除时，如何使 PostgreSQL 在表中插入一行？

提问by Benson Jin

回答by Erwin Brandstetter

Key elements

关键要素

About visibility

关于可见性

回答by Borys

相关推荐

最近更新

标签

当从另一个表中删除时，如何使 PostgreSQL 在表中插入一行？

提问by Benson Jin

回答by Erwin Brandstetter

Key elements

关键要素

About visibility

关于可见性

回答by Borys

相关推荐

postgresql PG 8.3，磁盘已满，服务器无法启动

PostgreSQL - 返回键值对数组的最佳方式

postgresql 将两个不相关的视图合并为一个视图

PostgreSQL 计数数组值

相关推荐

最近更新

标签