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;