删除后在远程数据库中插入行的触发器

Question

我创建了一个触发器，其工作原理如下：
从表中删除数据后flux_tresorerie_historique它将此行插入位于另一个数据库存档中的表flux_tresorerie_historique中

我使用dblink在远程数据库中插入数据，问题是创建查询太难了，尤其是表包含20列以上，我想为10个其他表创建类似的函数。

有没有其他快速的方法来确保这项任务？

这里有一个很好的例子：

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
RETURNS trigger AS
$BODY$
DECLARE date_rapprochement_flux TEXT;
DECLARE code_commission  TEXT;
DECLARE reference_flux TEXT;
BEGIN
IF OLD.date_rapprochement_flux is null
THEN
date_rapprochement_flux = 'NULL';
ELSE
date_rapprochement_flux = ''''||to_char(OLD.date_rapprochement_flux, 'YYYY-MM-DD')||'''';
END IF;

IF OLD.code_commission is null
THEN
code_commission = 'NULL';
ELSE
code_commission = ''''||replace(OLD.code_commission,'''','''''')||'''';
END IF;

IF OLD.reference_flux is null
THEN
reference_flux = 'NULL';
ELSE
reference_flux = ''''||replace(OLD.reference_flux,'''','''''')||'''';
END IF;

perform dblink_connect('dbname=gtr_bd_archive user=postgres     password=postgres');
perform dblink_exec('insert into flux_tresorerie_historique values('||OLD.id_flux_historique||','''||OLD.date_operation_flux||''','''||OLD.date_valeur_flux||''','||date_rapprochement_flux||','''||replace(OLD.libelle_flux,'''','''''')||''','||OLD.montant_flux||','||OLD.contre_valeur_dzd||','''||replace(OLD.rib_compte_bancaire,'''','''''')||''','||OLD.frais_flux||','''||replace(OLD.sens_flux,'''','''''')||''','''||replace(OLD.statut_flux,'''','''''')||''','''||replace(OLD.code_devise,'''','''''')||''','''||replace(OLD.code_mode_paiement,'''','''''')||''','''||replace(OLD.code_agence,'''','''''')||''','''||replace(OLD.code_compte,'''','''''')||''','''||replace(OLD.code_banque,'''','''''')||''','''||OLD.date_maj_flux||''','''||replace(OLD.statut_frais,'''','''''')||''','||reference_flux||','||code_commission||','||OLD.id_flux||');');
perform dblink_disconnect();
RETURN NULL;
END;

Answer 1

您可以使用quote_nullable进行此操作！此外，concat_ws非常方便：

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
RETURNS trigger AS
$BODY$
BEGIN
  perform dblink_connect('dbname=gtr_bd_archive user=postgres password=postgres');
  perform dblink_exec('insert into flux_tresorerie_historique values('||
    concat_ws(', ', quote_nullable(OLD.id_flux_historique),
                    quote_nullable(OLD.date_operation_flux),
                    quote_nullable(OLD.date_valeur_flux),
                    quote_nullable(to_char(OLD.date_rapprochement_flux, 'YYYY-MM-DD')),
                    quote_nullable(OLD.libelle_flux),
                    quote_nullable(OLD.montant_flux),
                    quote_nullable(OLD.contre_valeur_dzd),
                    quote_nullable(OLD.rib_compte_bancaire),
                    quote_nullable(OLD.frais_flux),
                    quote_nullable(OLD.sens_flux),
                    quote_nullable(OLD.statut_flux),
                    quote_nullable(OLD.code_devise),
                    quote_nullable(OLD.code_mode_paiement),
                    quote_nullable(OLD.code_agence),
                    quote_nullable(OLD.code_compte),
                    quote_nullable(OLD.code_banque),
                    quote_nullable(OLD.date_maj_flux),
                    quote_nullable(OLD.statut_frais),
                    quote_nullable(OLD.reference_flux),
                    quote_nullable(OLD.code_commission),
                    quote_nullable(OLD.id_flux)
           )||');');
  perform dblink_disconnect();
  RETURN NULL;
END;

请注意，在单引号之间放置非刺值是可以的，因为带引号的文字对于PostgreSQL来说与不带引号的文字值一样好，因此放置由quote_nullable处理的所有列很方便。另请注意，quote_nullable已经以YYYY-MM-DD格式输出日期（例如选择quote_nullable（现在（）::date）将导致'2016-05-04'），因此您可能希望通过删除to_char来进一步简化OLD.date_rapprochement_flux。

Answer 2

这是复制的有限应用。需求变化很大，因此有许多不同的既定解决方案来解决不同的情况。考虑手册中的概述。

您手工编织的、基于触发器的解决方案是相对较少删除的一个可行选择。为每一行打开和关闭单独的连接会产生相当大的开销。还有其他各种选择。

在使用 dblink 时，我建议进行一些修改。最重要的是：

>

使用format（）更优雅地转义字符串。

传递整行，而不是传递和转义每一列。

不要将密码放在每个触发器函数中
使用＜code＞FOREIGN SERVER＜/code＞和＜code＞USER MAPPING＜/code>。详细说明如下：

< li >即使函数中止，也在UDF中持续插入

基本上，在源服务器上运行一次：

CREATE SERVER myserver FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (hostaddr '127.0.0.1', dbname 'gtr_bd_archive');

CREATE USER MAPPING FOR role_source SERVER myserver
OPTIONS (user 'postgres', password 'secret');

最好不要在目标服务器上以超级用户身份登录。使用具有有限权限的专用角色以避免权限提升。

并使用目标服务器上的密码文件来允许无密码访问。这样，您甚至不必将密码存储在用户映射中。此相关答案的最后一章中的说明：

• 使用不带密码的psql命令运行批处理文件

然后:

CREATE OR REPLACE FUNCTION pg_temp.flux_tresorerie_historique_backup_row()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server from above

   PERFORM dblink_exec( format(
   $$
   INSERT INTO flux_tresorerie_historique  -- provide target column list!
   SELECT (r).id_flux_historique
        , (r).date_operation_flux
        , (r).date_valeur_flux
        , (r).date_rapprochement_flux::date  -- 'YYYY-MM-DD' is default ISO format anyway
        , (r).libelle_flux
        , (r).montant_flux
        , (r).contre_valeur_dzd
        , (r).rib_compte_bancaire
        , (r).frais_flux
        , (r).sens_flux
        , (r).statut_flux
        , (r).code_devise
        , (r).code_mode_paiement
        , (r).code_agence
        , (r).code_compte
        , (r).code_banque
        , (r).date_maj_flux
        , (r).statut_frais
        , (r).reference_flux
        , (r).code_commission
        , (r).id_flux
   FROM   (SELECT %L::flux_tresorerie_historique) t(r)
   $$, OLD::text));  -- cast whole row type

   PERFORM dblink_disconnect();
   RETURN NULL;  -- only for AFTER trigger
END
$func$  LANGUAGE plpgsql;

如果行类型不匹配，则应拼写出目标表的列列表。

如果你认真对待这一点：

将此行插入表flux_tresorerie_historique

即，您插入整行并且目标行类型相同（无需从时间戳中提取日期等），您可以进一步简化传递整行。

CREATE OR REPLACE FUNCTION flux_tresorerie_historique_backup_row()
  RETURNS trigger AS
$func$
BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server

   PERFORM dblink_exec( format(
   $$
   INSERT INTO flux_tresorerie_historique
   SELECT (%L::flux_tresorerie_historique).*
   $$
   , OLD::text));

   PERFORM dblink_disconnect();
   RETURN NULL;  -- only for AFTER trigger
END
$func$  LANGUAGE plpgsql;

相关：

< li >如何在PostgreSQL中进行大型非阻塞更新？