Question

(说明:更新,后经通过回答>。

关于邮政总局的8.1(或以后)分门别类表格,如果<代码>,则如何界定<编码>>UPDATE的触发和程序,将记录从一个部分移至另一个部分。 UPDATE系指对界定隔离的有限领域作出改变?

例如,我印发一份表格记录,分为积极和被动的记录,如:

create table RECORDS (RECORD varchar(64) not null, ACTIVE boolean default true);
create table ACTIVE_RECORDS   ( check (ACTIVE) ) inherits RECORDS;
create table INACTIVE_RECORDS ( check (not ACTIVE) ) inherits RECORDS;

触发和运行良好:新的积极记录在一张桌上,新的不活动记录在另一个桌上。我愿<条码>。 UPDATEs to the ACTIVE field to "move” a Records from one children table to the other, but being Expoing an wrong which indicated that this may not be possible.

3. 夸大规格和错误信息:

pg=> CREATE OR REPLACE FUNCTION record_update()
     RETURNS TRIGGER AS $$
     BEGIN
       IF (NEW.active = OLD.active) THEN
         RETURN NEW;
       ELSIF (NEW.active) THEN
         INSERT INTO active_records VALUES (NEW.*);
         DELETE FROM inactive_records WHERE record = NEW.record;
       ELSE
         INSERT INTO inactive_records VALUES (NEW.*);
         DELETE FROM active_records WHERE record = NEW.record;
       END IF;
       RETURN NULL;
     END;
     $$
     LANGUAGE plpgsql;
     
pg=> CREATE TRIGGER record_update_trigger
       BEFORE UPDATE ON records
       FOR EACH ROW EXECUTE PROCEDURE record_update();

pg=> select * from RECORDS;
record | active 
--------+--------
foo    | t         --  foo  record actually in table ACTIVE_RECORDS
bar    | f         --  bar  record actually in table INACTIVE_RECORDS
(2 rows)

pg=> update RECORDS set ACTIVE = false where RECORD =  foo ;
ERROR:  new row for relation "active_records" violates check constraint "active_records_active_check"

采用触发程序(恢复民族解放军,等等)向我建议,在援引我的触发因素之前,必须检查制约因素,并查明错误,这意味着我目前的做法赢得了一定的工作。这样做能够奏效?

pg s [list partitioning <>/em>] ***

-- untested! create table RECORDS (..., ACTIVE boolean...) partition by list(ACTIVE) ( partition ACTIVE_RECORDS values (true), partition INACTIVE_RECORDS values (false) )

http://www.ohchr.org。

以下为<编码>UPDATE触发程序。我最后采用分配给各分区的相同程序。信贷完全是Bell,其答案使我对触发分部分分配的关键见解:

CREATE OR REPLACE FUNCTION record_update() RETURNS TRIGGER AS $$ BEGIN IF ( (TG_TABLE_NAME = active_records AND NOT NEW.active) OR (TG_TABLE_NAME = inactive_records AND NEW.active) ) THEN DELETE FROM records WHERE record = NEW.record; INSERT INTO records VALUES (NEW.*); RETURN NULL; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

Answer 1

It can be made to work, the trigger that does the move just needs to be defined for each partition, not the whole table. So start as you did for table definitions and the INSERT trigger

CREATE TABLE records (
 record varchar(64) NOT NULL,
 active boolean default TRUE
);

CREATE TABLE active_records (CHECK (active)) INHERITS (records);
CREATE TABLE inactive_records (CHECK (NOT active)) INHERITS (records);

CREATE OR REPLACE FUNCTION record_insert()
RETURNS TRIGGER AS $$
BEGIN
  IF (TRUE = NEW.active) THEN
    INSERT INTO active_records VALUES (NEW.*);
  ELSE
    INSERT INTO inactive_records VALUES (NEW.*);
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER record_insert_trigger
 BEFORE INSERT ON records
 FOR EACH ROW EXECUTE PROCEDURE record_insert();

......某些测试数据......

INSERT INTO records VALUES ( FirstLittlePiggy , TRUE);
INSERT INTO records VALUES ( SecondLittlePiggy , FALSE);
INSERT INTO records VALUES ( ThirdLittlePiggy , TRUE);
INSERT INTO records VALUES ( FourthLittlePiggy , FALSE);
INSERT INTO records VALUES ( FifthLittlePiggy , TRUE);

现在是分治的触发因素。 New.active = OLD.active 核对工作是否含蓄,因为我们知道在表上首先允许什么。

CREATE OR REPLACE FUNCTION active_partition_constraint()
  RETURNS TRIGGER AS $$
    BEGIN
      IF NOT (NEW.active) THEN
        INSERT INTO inactive_records VALUES (NEW.*);
        DELETE FROM active_records WHERE record = NEW.record;
        RETURN NULL;
      ELSE
        RETURN NEW;
      END IF;
    END;
    $$
    LANGUAGE plpgsql;

CREATE TRIGGER active_constraint_trigger
  BEFORE UPDATE ON active_records
  FOR EACH ROW EXECUTE PROCEDURE active_partition_constraint();

CREATE OR REPLACE FUNCTION inactive_partition_constraint()
  RETURNS TRIGGER AS $$
    BEGIN
      IF (NEW.active) THEN
        INSERT INTO active_records VALUES (NEW.*);
        DELETE FROM inactive_records WHERE record = NEW.record;
        RETURN NULL;
      ELSE
        RETURN NEW;
      END IF;
    END;
    $$
    LANGUAGE plpgsql;

CREATE TRIGGER inactive_constraint_trigger
  BEFORE UPDATE ON inactive_records 
  FOR EACH ROW EXECUTE PROCEDURE inactive_partition_constraint();

......并测试结果......

scratch=> SELECT * FROM active_records;
      record      | active 
------------------+--------
 FirstLittlePiggy | t
 ThirdLittlePiggy | t
 FifthLittlePiggy | t
(3 rows)

scratch=> UPDATE records SET active = FALSE WHERE record =  ThirdLittlePiggy ;
UPDATE 0
scratch=> SELECT * FROM active_records;
      record      | active 
------------------+--------
 FirstLittlePiggy | t
 FifthLittlePiggy | t
(2 rows)

scratch=> SELECT * FROM inactive_records;
      record       | active 
-------------------+--------
 SecondLittlePiggy | f
 FourthLittlePiggy | f
 ThirdLittlePiggy  | f
(3 rows)

Answer 2

Beware that you can partition by list and let the database do all the hard work to move rows among partitions. (untested for 8.4 but most probably working, as for pilcrow comment).

在以下例子中,使用主轴中的一栏制作并分拨by list。

create table t (
  -- natural primary key
  doc_type varchar not null default  PRODUCT ,
  doc_id   int not null generated always as identity,
  
  -- content columns
  title    varchar not null,
  
  -- primary key
  primary key (doc_type, doc_id)
)
partition by list(doc_type);

-- partitions of t
create table t_product partition of t for values in ( PRODUCT );
create table t_default partition of t default;

然后,根据<代码>doc_type的价值,在t_product/code>或上添加一些数据。


insert into t (doc_type, title) values
( PRODUCT ,  My first product ),  -- 1
( ARTICLE ,  My first article ),  -- 2
( TOPIC ,    My first topic ),    -- 3
( PRODUCT ,  My second product ), -- 4
( PRODUCT ,  My third product ),  -- 5
( ARTICLE ,  My second article ), -- 6
( TOPIC ,    My second topic ),   -- 7
( PRODUCT ,  My fourth product ); -- 8

我们的浏览量自动转移到右桌上。
select * from t_product;

doc_type|doc_id|title            |
--------+------+-----------------+
PRODUCT |     1|My first product |
PRODUCT |     4|My second product|
PRODUCT |     5|My third product |
PRODUCT |     8|My fourth product|

现在,让我们将<条码>PRODUCT转换成<>ARTICLE,以了解情况。

update t
set    doc_type =  ARTICLE 
where  doc_type =  PRODUCT 
and    doc_id = 1;

浏览量不见t_product>> 分辨率

select * from t_product;
doc_type|doc_id|title            |
--------+------+-----------------+
PRODUCT |     4|My second product|
PRODUCT |     5|My third product |
PRODUCT |     8|My fourth product|

但在<代码>t_default部分。

doc_type|doc_id|title            |
--------+------+-----------------+
ARTICLE |     2|My first article |
TOPIC   |     3|My first topic   |
ARTICLE |     6|My second article|
TOPIC   |     7|My second topic  |
ARTICLE |     1|My first product |

友情链接