在特定条件下获取行以交换表

我目前有一个父表：

CREATE TABLE members (
    member_id SERIAL NOT NULL, UNIQUE, PRIMARY KEY
    first_name varchar(20)
    last_name varchar(20)
    address address (composite type)
    contact_numbers varchar(11)[3]
    date_joined date
    type varchar(5)
);

和两个相关表：

CREATE TABLE basic_member (
    activities varchar[3])
    INHERITS (members)
);

CREATE TABLE full_member ( 
    activities varchar[])
    INHERITS (members)
);

如果类型是full详细信息被输入到full_member表或如果类型是basic进入basic_member桌子。我想要的是，如果我运行更新并将类型更改为basic or full该元组进入相应的表。

我想知道我是否可以用如下规则来做到这一点：

 CREATE RULE tuple_swap_full
 AS ON UPDATE TO full_member
 WHERE new.type = 'basic'
 INSERT INTO basic_member VALUES (old.member_id, old.first_name, old.last_name,
 old.address, old.contact_numbers, old.date_joined, new.type, old.activities);

...然后从 full_member 中删除该记录

只是想知道我的规则是否接近或是否有更好的方法。

• 你不需要

  member_id SERIAL NOT NULL, UNIQUE, PRIMARY KEY
  

  A PRIMARY KEY暗示UNIQUE NOT NULL自动地：

  member_id SERIAL PRIMARY KEY
  

• 我不会使用硬编码的最大长度varchar(20)。只需使用text http://www.postgresql.org/docs/current/interactive/datatype-character.html如果确实必须强制执行最大长度，请添加检查约束。更容易改变。

• 语法为INHERITS被损坏了。关键字位于列周围的括号之外。

  CREATE TABLE full_member ( 
      activities text[]
  ) INHERITS (members);
  

• 表名不一致（membersmember）。我在测试用例中到处使用单数形式。

• 最后，我不会使用规则来完成任务。 Atrigger AFTER UPDATE似乎更可取。

考虑以下

测试用例：

Tables:

CREATE SCHEMA x;  -- I put everything in a test schema named "x".

-- DROP TABLE x.members CASCADE;
CREATE TABLE x.member (
     member_id SERIAL PRIMARY KEY
    ,first_name text
    -- more columns ...
    ,type text);

CREATE TABLE x.basic_member (
    activities text[3]
) INHERITS (x.member);

CREATE TABLE x.full_member ( 
    activities text[]
) INHERITS (x.member);

触发功能：

数据修改 CTE http://www.postgresql.org/docs/current/interactive/queries-with.html#QUERIES-WITH-MODIFYING (WITH x AS ( DELETE ..）是实现此目的的最佳工具。需要 PostgreSQL 9.1 或更高版本。
对于旧版本，首先INSERT then DELETE.

CREATE OR REPLACE FUNCTION x.trg_move_member()
  RETURNS trigger AS
$BODY$
BEGIN

CASE NEW.type
WHEN 'basic' THEN
    WITH x AS (
        DELETE FROM x.member
        WHERE member_id = NEW.member_id
        RETURNING *
        )
    INSERT INTO x.basic_member (member_id, first_name, type) -- more columns
    SELECT member_id, first_name, type -- more columns
    FROM   x;

WHEN 'full' THEN
    WITH x AS (
        DELETE FROM x.member 
        WHERE member_id = NEW.member_id
        RETURNING *
        )
    INSERT INTO x.full_member (member_id, first_name, type) -- more columns
    SELECT member_id, first_name, type -- more columns
    FROM   x;
END CASE;

RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Trigger:

请注意，它是一个AFTER触发器并有一个WHEN健康）状况。WHEN条件需要 PostgreSQL 9.0 或更高版本。对于早期版本，您可以将其保留，触发器本身中的 CASE 语句会处理它。

CREATE TRIGGER up_aft
  AFTER UPDATE
  ON x.member
  FOR EACH ROW
  WHEN (NEW.type IN ('basic ','full')) -- OLD.type cannot be IN ('basic ','full')
  EXECUTE PROCEDURE x.trg_move_member();

Test:

INSERT INTO x.member (first_name, type) VALUES ('peter', NULL);

UPDATE x.member SET type = 'full' WHERE first_name = 'peter';
SELECT * FROM ONLY x.member;
SELECT * FROM x.basic_member;
SELECT * FROM x.full_member;