Get row to swap tables on a certain condition

Question

I currently have a parent table:

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)
);

and two related tables:

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

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

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);

... then delete the record from the full_member

Just wondering if my rule is anywhere near or if there is a better way.

Answer 1

• You don't need

  member_id SERIAL NOT NULL, UNIQUE, PRIMARY KEY
  

  A PRIMARY KEY implies UNIQUE NOT NULL automatically:

  member_id SERIAL PRIMARY KEY
  

• 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.

• Syntax for INHERITS is mangled. The key word goes outside the parens around columns.

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

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

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

Consider the following

Test case:

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);

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;

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;