获取行以在特定条件下交换表 [英] Get row to swap tables on a certain condition

问题描述

我目前有一个父表：

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 表。我想要的是，如果我运行更新并更改类型为基本或完全元组进入相应表。

If the type is full the details are entered to the full_member table or if type is basic into the basic_member table. What I want is that if I run an update and change the type to basic or full the tuple goes into the corresponding table.

我想知道我是否可以这样做：

I was wondering if I could do this with a rule like:

 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中删除记录

... then delete the record from the full_member

推荐答案

• 您不需要

  • You don't need

    member_id SERIAL NOT NULL, UNIQUE, PRIMARY KEY
    

    A PRIMARY KEY 意味着 UNIQUE NOT NULL 自动：

    A PRIMARY KEY implies UNIQUE NOT NULL automatically:

    member_id SERIAL PRIMARY KEY
    

  
  

  • 我不会使用硬编码的最大长度 varchar（20）。只需使用 text 并添加一个检查约束，如果你真的必须强制最大长度。更容易改变。

  • I wouldn't use hard coded max length of varchar(20). Just use text and add a check constraint if you really must enforce a maximum length. Easier to change around.

    INHERITS 的句法被破坏。

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

  
  

  • 表名称不一致（会员< - > member ）。我在测试用例中使用单数形式。

  • Table names are inconsistent (members <-> member). I use the singular form everywhere in my test case.

    最后，我不会为任务使用一个RULE。 触发 AFTER UPDATE 似乎更可取。

    Finally, I would not use a RULE for the task. A trigger AFTER UPDATE seems preferable.

    请考虑以下

    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 （ WITH x AS（DELETE .. ）是最好的工具，需要PostgreSQL 9.1或更高版本。
    
    对于旧版本，首先 INSERT 然后 DELETE 。

    Trigger function:

    Data-modifying CTEs (WITH x AS ( DELETE ..) are the best tool for the purpose. Requires PostgreSQL 9.1 or later.
    For older versions, first 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;
    

    
    
    

    触发器：

    
    
    

    AFTER 触发并具有 WHEN 条件。
    WHEN 条件需要PostgreSQL 9.0或更高版本。对于早期版本，您可以将其移除，触发器本身中的CASE语句会处理它。

    Trigger:

    Note that it is an AFTER trigger and has a WHEN condition. WHEN condition requires PostgreSQL 9.0 or later. For earlier versions, you can just leave it away, the CASE statement in the trigger itself takes care of it.

    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;
    

    这篇关于获取行以在特定条件下交换表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！