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 函数

database - 主-主 vs 主-从数据库架构？
我听说过两种数据库架构。大师级主从 master-master不是更适合现在的web吗，因为它就像Git一样，每个单元都有整套数据，如果一个宕机也无所谓。主从让我想起了 SVN(我不喜欢它)，你
mysql 复制主-主-主
我们当前将 MySQL 配置为支持故障转移:Site1 Site2。当它们被设置为主/主时。在给定时间点，应用程序服务器仅主动写入一个站点。我们想要设置一个新的故障转移站点。然后我们将拥有 Site
database - 主-主 vs 主从数据库架构？
我听说过两种数据库架构。大师-大师主从 master-master 不是更适合当今的网络吗，因为它就像 Git，每个单元都有整套数据，如果其中一个发生故障，也没关系。主从让我想起 SVN(我不喜
MySQL:类别和子类别的交叉表引用(主、主、名称)？
我正在创建一个标记为类别的表，其中主类别(父列)包含 0，子类别包含父类别的 ID。我听说这叫引用。我的问题:这张表的结构正确吗？或者是否有更好的方法，例如实现遍历树或类似方法？ CREATE TAB
C++ 主 vs C 主
我正在阅读一份关于 C++ 与 C 的文档。该文档说与 C 相比，C++ 编写得非常紧凑。一个例子是，C 允许 main() 函数类型为 void。另一方面，C++ 不允许这样做，他给出了标准中的以下
java - C 主 vs Java 主
C main函数和Java main函数有什么区别？ int main( int argc, const char* argv[] ) 对比 public static void main(Strin
responsive-design - 3 列(左、主、右)站点，但较小的设备首先是主(主、左、右)
我一直摸不着头脑，但运气不好。设计器有一个包含 3 栏的站点、两个侧边栏和一个主要内容区域。专为桌面设计，左栏、主要内容、右栏。但是，在较小的设备上，我们希望首先堆叠主要内容。所以通常情况下，你可
Jenkins 主/从配置
我一直在阅读有关 Jenkins 主/从配置的信息，但我仍然有一些问题: 是不是真的没有像 Jenkins 主站那样安装和启动从站 Jenkins？我假设我会以相同的方式安装一个主 Jenkins 和
c# - 主/细节的通用Viewmodel模式
据我了解，Viemodel中MVVM背后的概念包括业务逻辑和/或诸如暴露于 View 的数据的主/明细关系之类的事物因此，正如我发现的那样，有很多ORM生成器，例如模型的telerik a.o以及另
elasticsearch - 主/副本计分不一致
我们有一个群集，其中包含3个主分区，每个主分区有2个副本。主/副本分片的总文档数相同；但是，对于同一查询/文档，我们得到3个不同的分数。当我们将preference = primary添加为查询参数时
java - 从指定目录启动Java应用程序(主)
我有一个非常大/旧/长时间运行的项目，它使用相对于启动目录的路径访问文件资源(即应用程序仅在从特定目录启动时才工作)。当我需要调试程序时，我可以从 eclipse 启动它并使用“运行配置”->->“工
c - 主/从设备出现段错误
谁能向我解释一下为什么我在这段代码上遇到段错误？我一直试图弄清楚这一点，但在各种搜索中却一无所获。当我运行代码而不调用 main(argc, argv) 时，它会运行。 Slave 仅将 argv 中
ios - 主 - 详细项目未按预期工作
使用 xcode 中的默认项目作为主从应用程序，如果我在折叠委托(delegate)中放置 print 调试语句，当我旋转设备时它似乎永远不会被触发(事实上我永远无法触发它)。我编辑的代码位于 Ap
mysql 主/从故障转移
是否有任何产品可以使 mysql 主/从故障转移过程更容易？一些可以自动发生的事情，而不是手动修复它。最佳答案 [...稍后...;) 你所说的“更容易”是什么？MySQL 有很多解决方案: MyS
MySQL 主/主复制仅以一种方式工作
我有两个 mysql 数据库。我想做主/主复制。复制以一种方式进行。然而，反过来说却不然。该错误表明它无法与用户“test@IPADDRESS”连接。如何将用户名更改为 repl？从未进行过测试，
MySQL 主-主复制
我正在尝试在 MySQL 中运行以下查询: GRANT REPLICATION SLAVE ON *.* TO 'replication'@’10.141.2.%’ IDENTIFIED BY ‘sl
android - 主/从流程中的操作栏菜单项
我正在尝试使用 Android 提供的主/详细流程模板创建一个应用程序，并且我正在尝试将多个操作栏菜单项添加到操作栏的主要部分和详细信息部分。这就是我要实现的目标: (来源:softwarecrew.
C++ 主/工
我正在寻找一个跨平台的 C++ master/worker 库或工作队列库。一般的想法是我的应用程序将创建某种任务或工作对象，将它们传递给工作主机或工作队列，这将依次在单独的线程或进程中执行工作。为了
MySQL 主/外键大小？
我似乎看到很多人在他们的 MySQL 模式中任意分配大尺寸的主/外键字段，例如 INT(11) 甚至 WordPress 使用的 BIGINT(20)。如果我错了，请纠正我，但即使是 INT(4)
sql - 主/外键与没有主键的单表的性能
如果我有一个可以与多个键相关联的用户，正确的表设置应该是: 一个表有两列，例如: UserName | Key 没有主键且用户可以有多行，或者: 具有匹配标识符的两个表 Table 1 Us

行者123

个人简介

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

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

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