PostgreSQL:更新主表上的记录并移动子分区中的记录-6ren

PostgreSQL:更新主表上的记录并移动子分区中的记录

转载作者：行者123 更新时间：2023-11-29 11:45:14

如何定义一个更新触发器函数来更新主表中定义了分区的记录。我有一个表，上面定义了分区和插入触发器函数的代码，只要主表中有插入，该函数就会在子表中插入数据。我的代码如下:

    --Master table
        CREATE TABLE "SMS_RECEIPT"
        (
          "ID" integer NOT NULL,
          "ACCOUNT_INFO" character varying(255),
          "CHARGE" character varying(255),
          "DELIVERY_INFO" character varying(255),
          "DESTINATION" character varying(255),
          "MESSAGE_ID" character varying(255),
          "RECEIPT_TYPE" integer,
          "SMS_CENTRE" character varying(255),
          "SMS_ID" character varying(255),
          "SOURCE" character varying(255),
          "STATUS" character varying(255),
          timedate date,
          "FLAG" character varying(255),
          "STID" character varying(255),
          CONSTRAINT "SMS_RECEIPT_pkey" PRIMARY KEY ("ID")
        );

--child partition tables
        CREATE TABLE SMS_RECEIPT_y2015m01 (
        CHECK ( timedate >=  '2015-01-01' AND timedate <  '2015-01-31' )
         ) INHERITS ("SMS_RECEIPT");
         CREATE TABLE SMS_RECEIPT_y2015m02 (
        CHECK ( timedate >=  '2015-02-01' AND timedate <  '2015-02-28' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m03 (
        CHECK ( timedate >=  '2015-03-01' AND timedate <  '2015-03-31' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m04 (
        CHECK ( timedate >=  '2015-04-01' AND timedate <  '2015-04-30' )
         ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m05 ( 
        CHECK ( timedate >=  '2015-05-01' AND timedate <  '2015-05-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m06 ( 
        CHECK ( timedate >=  '2015-06-01' AND timedate <  '2015-06-30' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m07 ( 
        CHECK ( timedate >=  '2015-07-01' AND timedate <  '2015-07-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m08 ( 
        CHECK ( timedate >=  '2015-08-01' AND timedate <  '2015-08-31' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m09 ( 
        CHECK ( timedate >=  '2015-09-01' AND timedate <  '2015-09-30' )
        ) INHERITS ("SMS_RECEIPT");
        CREATE TABLE SMS_RECEIPT_y2015m010 ( 
        CHECK ( timedate >=  '2015-10-01' AND timedate <  '2015-10-31' )
        ) INHERITS ("SMS_RECEIPT");


        ALTER TABLE  SMS_RECEIPT_y2015m01 ADD CONSTRAINT  SMS_RECEIPT_y2015m01_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m02 ADD CONSTRAINT  SMS_RECEIPT_y2015m02_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m03 ADD CONSTRAINT  SMS_RECEIPT_y2015m03_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m04 ADD CONSTRAINT  SMS_RECEIPT_y2015m04_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m05 ADD CONSTRAINT  SMS_RECEIPT_y2015m05_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m06 ADD CONSTRAINT  SMS_RECEIPT_y2015m06_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m07 ADD CONSTRAINT  SMS_RECEIPT_y2015m07_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m08 ADD CONSTRAINT  SMS_RECEIPT_y2015m08_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m09 ADD CONSTRAINT  SMS_RECEIPT_y2015m09_pkey PRIMARY KEY ("ID");
        ALTER TABLE  SMS_RECEIPT_y2015m010 ADD CONSTRAINT  SMS_RECEIPT_y2015m010_pkey PRIMARY KEY ("ID");


        CREATE INDEX idxSMS_RECEIPT_y2015m01_TIME_DATE ON SMS_RECEIPT_y2015m01 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m02_TIME_DATE ON SMS_RECEIPT_y2015m02 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m03_TIME_DATE ON SMS_RECEIPT_y2015m03 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m04_TIME_DATE ON SMS_RECEIPT_y2015m04 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m05_TIME_DATE ON SMS_RECEIPT_y2015m05 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m06_TIME_DATE ON SMS_RECEIPT_y2015m06 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m07_TIME_DATE ON SMS_RECEIPT_y2015m07 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m08_TIME_DATE ON SMS_RECEIPT_y2015m08 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m09_TIME_DATE ON SMS_RECEIPT_y2015m09 (timedate);
        CREATE INDEX idxSMS_RECEIPT_y2015m010_TIME_DATE ON SMS_RECEIPT_y2015m010 (timedate);


        CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
        RETURNS TRIGGER AS $$
        BEGIN
            IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate <  '2015-01-31' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate <  '2015-02-28' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate <  '2015-03-31' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate <  '2015-04-30' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate <  '2015-05-31' ) THEN
                INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate <  '2015-06-30' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate <  '2015-07-31' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate <  '2015-08-31' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate <  '2015-09-30' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
            ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate <  '2015-10-31' ) THEN
          INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
            ELSE
                RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
            END IF;
            RETURN NEW;
        END;
        $$
        LANGUAGE plpgsql;


        CREATE TRIGGER trigger_SMS_RECEIPT_insert
            BEFORE INSERT ON "SMS_RECEIPT"
            FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

现在我必须编写更新触发器函数，只要主表中有更新，记录就应该相应地跨分区移动。

目前当我在 master 表上写更新语句时，它抛出如下错误:

ERROR:  new row for relation "sms_receipt_y2015m03" violates check constraint "sms_receipt_y2015m03_timedate_check"
DETAIL:  Failing row contains (2, asdf, asdf, asdf, asdf, asdf, 1234, asdfasd, asdfasdf, adsfad, adsfasd, 2015-04-22, adsf, were).
********** Error **********

ERROR: new row for relation "sms_receipt_y2015m03" violates check constraint "sms_receipt_y2015m03_timedate_check"
SQL state: 23514
Detail: Failing row contains (2, asdf, asdf, asdf, asdf, asdf, 1234, asdfasd, asdfasdf, adsfad, adsfasd, 2015-04-22, adsf, were).

所以，我写了一个更新前的触发函数如下:

Create or replace function sms_receipt_func_update_trigger()
returns trigger as $$
declare total integer;
begin
        IF exists( select 1 from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID";

        ELSIF exists( select 1 from SMS_RECEIPT_y2015m02 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m02 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m03 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m03 where "ID"=OLD."ID";
        ELSIF exists( select 1 from SMS_RECEIPT_y2015m04 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m04 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m05 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m05 where "ID"=OLD."ID";
    ELSIF exists( select 1 from SMS_RECEIPT_y2015m06 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m06 where "ID"=OLD."ID";
        ELSIF exists( select 1 from SMS_RECEIPT_y2015m07 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m07 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m08 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m01 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m09 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m09 where "ID"=OLD."ID";

    ELSIF exists( select 1 from SMS_RECEIPT_y2015m010 where "ID"=OLD."ID" and timedate = OLD.timedate ) THEN
                delete from SMS_RECEIPT_y2015m010 where "ID"=OLD."ID";
            ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;


create trigger trigger_sms_receipt_update 
before update on "SMS_RECEIPT"
for each row execute procedure
sms_receipt_func_update_trigger();

但是，我仍然无法解决这个问题。谁能告诉我如何修改更新触发器，以便完成这项工作。提前致谢。

修改了这个，所以使用 View 而不是触发器。现在，它适用于插入方法。但是，如何处理更新的事情？

   CREATE TABLE "SMS_RECEIPT"
(
  "ID" integer NOT NULL,
  "ACCOUNT_INFO" character varying(255),
  "CHARGE" character varying(255),
  "DELIVERY_INFO" character varying(255),
  "DESTINATION" character varying(255),
  "MESSAGE_ID" character varying(255),
  "RECEIPT_TYPE" integer,
  "SMS_CENTRE" character varying(255),
  "SMS_ID" character varying(255),
  "SOURCE" character varying(255),
  "STATUS" character varying(255),
  timedate date,
  "FLAG" character varying(255),
  "STID" character varying(255),
  CONSTRAINT "SMS_RECEIPT_pkey" PRIMARY KEY ("ID")
);

create view "sms_receipt_view" as select * from "SMS_RECEIPT";

CREATE TABLE SMS_RECEIPT_y2015m01 (
CHECK ( timedate >=  '2015-01-01' AND timedate <  '2015-01-31' )
 ) INHERITS ("SMS_RECEIPT");
 CREATE TABLE SMS_RECEIPT_y2015m02 (
CHECK ( timedate >=  '2015-02-01' AND timedate <  '2015-02-28' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m03 (
CHECK ( timedate >=  '2015-03-01' AND timedate <  '2015-03-31' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m04 (
CHECK ( timedate >=  '2015-04-01' AND timedate <  '2015-04-30' )
 ) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m05 (
CHECK ( timedate >=  '2015-05-01' AND timedate <  '2015-05-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m06 (
CHECK ( timedate >=  '2015-06-01' AND timedate <  '2015-06-30' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m07 (
CHECK ( timedate >=  '2015-07-01' AND timedate <  '2015-07-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m08 (
CHECK ( timedate >=  '2015-08-01' AND timedate <  '2015-08-31' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m09 (
CHECK ( timedate >=  '2015-09-01' AND timedate <  '2015-09-30' )
) INHERITS ("SMS_RECEIPT");
CREATE TABLE SMS_RECEIPT_y2015m010 (
CHECK ( timedate >=  '2015-10-01' AND timedate <  '2015-10-31' )
) INHERITS ("SMS_RECEIPT");


CREATE UNIQUE INDEX SMS_RECEIPT_unique ON "SMS_RECEIPT" USING btree ("ID");

ALTER TABLE  SMS_RECEIPT_y2015m01 ADD CONSTRAINT SMS_RECEIPT_y2015m01_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m02 ADD CONSTRAINT SMS_RECEIPT_y2015m02_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m03 ADD CONSTRAINT SMS_RECEIPT_y2015m03_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m04 ADD CONSTRAINT SMS_RECEIPT_y2015m04_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m05 ADD CONSTRAINT SMS_RECEIPT_y2015m05_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m06 ADD CONSTRAINT SMS_RECEIPT_y2015m06_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m07 ADD CONSTRAINT SMS_RECEIPT_y2015m07_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m08 ADD CONSTRAINT SMS_RECEIPT_y2015m08_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m09 ADD CONSTRAINT SMS_RECEIPT_y2015m09_pkey PRIMARY KEY ("ID");
ALTER TABLE  SMS_RECEIPT_y2015m010 ADD CONSTRAINT SMS_RECEIPT_y2015m010_pkey PRIMARY KEY ("ID");


CREATE INDEX idxSMS_RECEIPT_y2015m01_TIME_DATE ON SMS_RECEIPT_y2015m01 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m02_TIME_DATE ON SMS_RECEIPT_y2015m02 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m03_TIME_DATE ON SMS_RECEIPT_y2015m03 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m04_TIME_DATE ON SMS_RECEIPT_y2015m04 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m05_TIME_DATE ON SMS_RECEIPT_y2015m05 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m06_TIME_DATE ON SMS_RECEIPT_y2015m06 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m07_TIME_DATE ON SMS_RECEIPT_y2015m07 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m08_TIME_DATE ON SMS_RECEIPT_y2015m08 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m09_TIME_DATE ON SMS_RECEIPT_y2015m09 (timedate);
CREATE INDEX idxSMS_RECEIPT_y2015m010_TIME_DATE ON SMS_RECEIPT_y2015m010 (timedate);


create or replace function sms_receipt_func_update_trigger()
returns trigger as $$
begin
    update "sms_receipt_view" set timedate = new.timedate where "ID"=new."ID";
return new;
end;
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN

    IF ( NEW.timedate >=  '2015-01-01' AND NEW.timedate < '2015-01-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-02-01' AND NEW.timedate < '2015-02-28' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-03-01' AND NEW.timedate < '2015-03-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-04-01' AND NEW.timedate < '2015-04-30' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-05-01' AND NEW.timedate < '2015-05-31' ) THEN
        INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-06-01' AND NEW.timedate < '2015-06-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-07-01' AND NEW.timedate < '2015-07-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-08-01' AND NEW.timedate < '2015-08-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-09-01' AND NEW.timedate < '2015-09-30' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
    ELSIF ( NEW.timedate >=  '2015-10-01' AND NEW.timedate < '2015-10-31' ) THEN
  INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;

    RETURN NEW;
END;
$$
LANGUAGE plpgsql;    

CREATE TRIGGER trigger_SMS_RECEIPT_insert
    Instead of INSERT OR UPDATE ON "sms_receipt_view"
    FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();

create trigger trigger_sms_receipt_update
instead of update on "sms_receipt_view"
for each row execute procedure sms_receipt_func_update_trigger();

最佳答案

问题是你需要使用触发器返回旧记录或新记录。

您需要一个而不是 触发器。但它仅适用于 View 。所以你需要:

创建 View (在master表上就可以)
在 View 上“代替”创建触发器
仅在 View 上更新和插入

请参阅此处的表格:http://www.postgresql.org/docs/9.4/static/sql-createtrigger.html

我测试了代码，发现几乎没有什么可以修复的。开始了。
首先，我插入一条记录进行测试:

INSERT INTO sms_receipt_view(
        "ID", "ACCOUNT_INFO", "CHARGE", "DELIVERY_INFO", "DESTINATION", 
        "MESSAGE_ID", "RECEIPT_TYPE", "SMS_CENTRE", "SMS_ID", "SOURCE", 
        "STATUS", timedate, "FLAG", "STID")
VALUES (1, 'acc_info', 'charge', 'delivery info', 'my dest', 
        'ssss id mess', 4, 'vodafone center', 'dff33', '3333 3 33333', 
        'ok', '20150601 15:25', 'myflag', 'stid');

其次，插入触发器必须在插入时触发，所以我运行代码:

DROP TRIGGER trigger_sms_receipt_insert ON sms_receipt_view;

CREATE TRIGGER trigger_sms_receipt_insert
  INSTEAD OF INSERT
  ON sms_receipt_view
  FOR EACH ROW
  EXECUTE PROCEDURE sms_receipt_func_insert_trigger();

然后我重写了更新触发器:

CREATE OR REPLACE FUNCTION sms_receipt_func_update_trigger()
  RETURNS trigger AS
$BODY$
begin
    --update "sms_receipt_view" set timedate = new.timedate where "ID"=new."ID";
    --check if I need to move record
    IF date_part('year', OLD.timedate) <> date_part('year', NEW.timedate) OR date_part('month', OLD.timedate) <> date_part('month', NEW.timedate) THEN
    DELETE FROM "SMS_RECEIPT" WHERE "ID" = NEW."ID";

    INSERT INTO sms_receipt_view ("ID", "ACCOUNT_INFO", "CHARGE", "DELIVERY_INFO", "DESTINATION", "MESSAGE_ID", "RECEIPT_TYPE",
       "SMS_CENTRE", "SMS_ID", "SOURCE", "STATUS", timedate, "FLAG", "STID")
    VALUES (NEW."ID", NEW."ACCOUNT_INFO", NEW."CHARGE", NEW."DELIVERY_INFO", NEW."DESTINATION", NEW."MESSAGE_ID", NEW."RECEIPT_TYPE",
       NEW."SMS_CENTRE", NEW."SMS_ID", NEW."SOURCE", NEW."STATUS", NEW.timedate, NEW."FLAG", NEW."STID"
    );
    ELSE
        UPDATE "SMS_RECEIPT"
        SET "ACCOUNT_INFO" = NEW."ACCOUNT_INFO",
        "CHARGE" = NEW."CHARGE",
        "DELIVERY_INFO" = NEW."DELIVERY_INFO",
        "DESTINATION" = NEW."DESTINATION",
        "MESSAGE_ID" = NEW."MESSAGE_ID",
        "RECEIPT_TYPE" = NEW."RECEIPT_TYPE",
        "SMS_CENTRE" = NEW."SMS_CENTRE",
        "SMS_ID" = NEW."SMS_ID",
        "SOURCE" = NEW."SOURCE",
        "STATUS" = NEW."STATUS",
        timedate = NEW.timedate,
        "FLAG" = NEW."FLAG",
        "STID" = NEW."STID"
        WHERE "ID" = NEW."ID";
    END IF;
return NULL;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sms_receipt_func_update_trigger()
  OWNER TO postgres;

首先我检查我们是否真的需要移动记录。如果没有，我只需通过主表进行更新。如果我需要移动我删除旧记录并在 View 中插入新记录(因此我触发插入 View )。
警告:在我的模拟中，我认为 key 永远不会改变。如果您需要更新 ID，则需要修改一些代码。

然后我测试了2个案例。在我的场景中，id 是:1

UPDATE sms_receipt_view
SET "SMS_CENTRE" = 'tim center'
--timedate = '20150501'
WHERE "ID" = 1;

UPDATE sms_receipt_view
SET "SMS_CENTRE" = 'tim center',
timedate = '20150501'
WHERE "ID" = 1;

进行一些测试，但应该可以。

关于PostgreSQL:更新主表上的记录并移动子分区中的记录，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/30545136/

文章推荐： mysql - 按整数值分类和 COUNT() 的 sql 优化

文章推荐： mysql - 查找今年第一季度加入的员工

文章推荐： postgresql - 在 PyCharm Database Navigator 中访问 PostGIS 函数

java - JFrame 中的 JPanel 中的 JScrollPane 中的 JTextPane
我想做的是让 JTextPane 在 JPanel 中占用尽可能多的空间。对于我使用的 UpdateInfoPanel: public class UpdateInfoPanel extends JP
java - JFrame 中的 JPanel 中的 JTextArea 中的 JScrollPane 出现问题
我在 JPanel 中有一个 JTextArea，我想将其与 JScrollPane 一起使用。我正在使用 GridBagLayout。当我运行它时，框架似乎为 JScrollPane 腾出了空间，但
ios - iOs Xcode 中的 UIViewController 中的 UIView 中的 UITableView
我想在 xcode 中实现以下功能。我有一个 View Controller 。在这个 UIViewController 中，我有一个 UITabBar。它们下面是一个 UIView。将 UITab
sql - 与 SQL 中的 STUFF 等效的函数(MySQL 中的 GROUP_CONCAT/Oracle 中的 LISTAGG)
有谁知道Firebird 2.5有没有类似于SQL中“STUFF”函数的功能？我有一个包含父用户记录的表，另一个表包含与父相关的子用户记录。我希望能够提取用户拥有的“ROLES”的逗号分隔字符串，而
Mirth 中的 Json 解析或 Mirth 中的 Json 或 Mirth 中的 HL7 到 JSON
我想使用 JSON 作为 mirth channel 的输入和输出，例如详细信息保存在数据库中或创建 HL7 消息。简而言之，输入为 JSON 解析它并输出为任何格式。最佳答案 var objec
python - 如果文件 1 中的 A 列 = 文件 2 中的 A 列，则替换为文件 2 中的 B 列
通常我会使用 R 并执行 merge.by，但这个文件似乎太大了，部门中的任何一台计算机都无法处理它! (任何从事遗传学工作的人的附加信息)本质上，插补似乎删除了 snp ID 的 rs 数字，我只剩
Javascript 中的 HAML 中的 Javascript
我有一个以前可能被问过的问题，但我很难找到正确的描述。我希望有人能帮助我。在下面的代码中，我设置了varprice，我想添加javascript变量accu_id以通过rails在我的数据库中查找记
HTML 中的 SVG 中的 HTML
我有一个简单的 SVG 文件，在 Firefox 中可以正常查看 - 它的一些包装文本使用 foreignObject 包含一些 HTML - 文本包装在 div 中:
ruby - Ruby 中的 If block 中的 "Or"
所以我正在为学校编写一个 Ruby 程序，如果某个值是 1 或 3，则将 bool 值更改为 true，如果是 0 或 2，则更改为 false。由于我有 Java 背景，所以我认为这段代码应该有效:
amazon-web-services - 如何从账户 A 中的 Lambda(VPC 中的 Lambda)调用账户 B(VPC 中的此 Lambda)中的 AWS Lambda 函数
我做了什么: 我在这些账户之间创建了 VPC 对等连接互联网网关也连接到每个 VPC 还配置了路由表(以允许来自双方的流量) 情况1: 当这两个 VPC 在同一个账户中时，我成功测试了从另一个 La
php - 如何获取 column1 中的 value1 和 column2 中的 value2 但 column1 中的 value2 在 column2 中没有 value1 的行？
我有一个名为 contacts 的表: user_id contact_id 10294 10295 10294 10293 10293 10294 102
php - Magento 中的 foreach 中的 getChildHtml
我正在使用 Magento 中的新模板。为避免重复代码，我想为每个产品预览使用相同的子模板。特别是我做了这样一个展示: $products = Mage::getModel('catalog/pro
protocols - Elixir 中的 "for"中的 "defimpl"实际上检查了什么？
“for”是否总是检查协议(protocol)中定义的每个函数中第一个参数的类型？编辑(改写): 当协议(protocol)方法只有一个参数时，根据该单个参数的类型(直接或任意)找到实现。当协议(p
javascript - PHP 中的 JavaScript 中的 PHP
我想从我的 PHP 代码中调用 JavaScript 函数。我通过使用以下方法实现了这一点: echo ' drawChart($id); '; 这工作正常，但我想从我的 PHP 代码中获取数据，我使
javascript - html 中的 html 中的 JavaScript
这个问题已经有答案了: Event binding on dynamically created elements? (23 个回答) 已关闭 5 年前。我有一个动态表单，我想在其中附加一些其他 h
javascript - componentDidMount() 中的 .map 中的 setState
我正在尝试找到一种解决方案，以在 componentDidMount 中的映射项上使用 setState。我正在使用 GraphQL连同 Gatsby返回许多 data 项目，但要求在特定的 pat
android - ScrollView 中的 View 中的 OnTouchListener
我在 ScrollView 中有一个 View 。只要用户按住该 View ，我想每 80 毫秒调用一次方法。这是我已经实现的: final Runnable vibrate = new Runnab
android - GetStringUTFChars 中的 dvmDecodeIndirectRef 中的 dvmAbort
我用 jni 开发了一个 android 应用程序。我在 GetStringUTFChars 的 dvmDecodeIndirectRef 中得到了一个 dvmabort。我只中止了一次。为什么会这
android - Activity 中的 FragmentPagerAdapter 中的 RecyclerView
当我到达我的 Activity 时，我调用 FragmentPagerAdapter 来处理我的不同选项卡。在我的一个选项卡中，我想显示一个 RecyclerView，但他从未出现过，有了断点，我看到
android - Activity 中的 DialogFragment 中的 RecyclerView
当我按下 Activity 中的按钮时，会弹出一个 DialogFragment。在对话框 fragment 中，有一个看起来像普通 ListView 的 RecyclerView。我想要的行为是当

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

PostgreSQL:更新主表上的记录并移动子分区中的记录