postgresql - usuarios - Obtener una fila para intercambiar tablas en una determinada condición

monitorear rendimiento postgres

Question

Actualmente tengo una tabla padre:

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

y dos tablas relacionadas:

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

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

Si el tipo está full los detalles se ingresan en la tabla full_member o si type es basic en la tabla basic_member . Lo que quiero es que si ejecuto una actualización y cambie el tipo a basic o full la tupla vaya a la tabla correspondiente.

Me preguntaba si podría hacer esto con una regla como:

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

... luego borre el registro del miembro_completo

Me pregunto si mi regla está cerca o si hay una mejor manera.

Answer 1

• No necesitas

  member_id SERIAL NOT NULL, UNIQUE, PRIMARY KEY

  UNA PRIMARY KEY implica UNIQUE NOT NULL automáticamente:

  member_id SERIAL PRIMARY KEY

• No usaría la longitud máxima codificada de varchar(20) . Simplemente use text y agregue una restricción de verificación si realmente debe aplicar una longitud máxima. Más fácil de cambiar alrededor.

• La sintaxis para INHERITS está destrozada. La palabra clave sale de los parens alrededor de las columnas.

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

• Los nombres de tabla son inconsistentes ( members <-> member ). Uso el formulario singular en todas partes en mi caso de prueba.

• Finalmente, no usaría una REGLA para la tarea. Un disparador AFTER UPDATE parece preferible.

Considera lo siguiente

Caso de prueba:

Mesas:

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

Función de disparo:

Los CTE modificadores de datos ( WITH x AS ( DELETE .. ) son la mejor herramienta para este fin. Requiere PostgreSQL 9.1 o posterior.
Para versiones anteriores, primero INSERT y 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;

Desencadenar:

Tenga en cuenta que es un disparador AFTER y tiene una condición WHEN . WHEN condición requiere PostgreSQL 9.0 o posterior. Para versiones anteriores, puede dejarlo, la sentencia CASE en el desencadenador se ocupa de ello.

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

Prueba:

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;