sintaxis - trigger postgresql

¿Cómo pasar un registro a una función PL/pgSQL?

Question

Tengo 8 funciones PL / pgSQL similares; se utilizan como INSTEAD OF INSERT/UPDATE/DELETE desencadenantes en vistas para que puedan escribirse. Las vistas combinan columnas de una tabla genérica (llamadas "cosas" en el ejemplo a continuación) y una tabla especial ("cosas_conformadas" y "saboreadas_contenidas" a continuación). La característica de herencia de PostgreSQL no se puede usar en nuestro caso, por cierto.

Los desencadenantes tienen que insertar / actualizar filas en la tabla genérica; estas partes son idénticas en todas las 8 funciones. Dado que la tabla genérica tiene ~ 30 columnas, intento usar una función de ayuda allí, pero estoy teniendo problemas para pasar el registro NEW la vista a una función que necesita un registro de things como entrada.

(Se han formulado preguntas similares aquí y aquí , pero no creo que pueda aplicar las soluciones sugeridas en mi caso).

Esquema simplificado

CREATE TABLE things (
    id    SERIAL  PRIMARY KEY,
    name  TEXT    NOT NULL
    -- (plus 30 more columns)
);
CREATE TABLE flavored_things (
    thing_id  INT   PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
    flavor    TEXT  NOT NULL
);
CREATE TABLE shaped_things (
    thing_id  INT   PRIMARY KEY REFERENCES things (id) ON DELETE CASCADE,
    shape     TEXT  NOT NULL
);
-- etc...

Implementación de vista escritura para saborear_objetos

CREATE VIEW flavored_view AS
    SELECT t.*,
           f.*
      FROM things t
      JOIN flavored_things f ON f.thing_id = t.id;

CREATE FUNCTION flavored_trig () RETURNS TRIGGER AS $fun$
DECLARE
    inserted_id  INT;
BEGIN
    IF TG_OP = ''INSERT'' THEN
        INSERT INTO things VALUES (  -- (A)
            DEFAULT,
            NEW.name
            -- (plus 30 more columns)
        ) RETURNING id INTO inserted_id;
        INSERT INTO flavored_things VALUES (
            inserted_id,
            NEW.flavor
        );
        RETURN NEW;
    ELSIF TG_OP = ''UPDATE'' THEN
        UPDATE things SET  -- (B)
            name = NEW.name
            -- (plus 30 more columns)
        WHERE id = OLD.id;
        UPDATE flavored_things SET
            flavor = NEW.flavor
        WHERE thing_id = OLD.id;
        RETURN NEW;
    ELSIF TG_OP = ''DELETE'' THEN
        DELETE FROM flavored_things WHERE thing_id = OLD.id;
        DELETE FROM things WHERE id = OLD.id;
        RETURN OLD;
    END IF;
END;
$fun$ LANGUAGE plpgsql;

CREATE TRIGGER write_flavored
    INSTEAD OF INSERT OR UPDATE OR DELETE ON flavored_view
    FOR EACH ROW EXECUTE PROCEDURE flavored_trig();

Las afirmaciones marcadas como "(A)" y "(B)" arriba son lo que me gustaría reemplazar con una llamada a una función auxiliar.

Función de ayuda para INSERTAR

Mi primer intento fue reemplazar la declaración "(A)" con

inserted_id = insert_thing(NEW);

usando esta función

CREATE FUNCTION insert_thing (new_thing RECORD) RETURNS INTEGER AS $fun$
DECLARE
    inserted_id  INT;
BEGIN
    INSERT INTO things (name) VALUES (
        new_thing.name
        -- (plus 30 more columns)
    ) RETURNING id INTO inserted_id;
    RETURN inserted_id;
END;
$fun$ LANGUAGE plpgsql;

Esto falla con el mensaje de error "Las funciones PL / pgSQL no pueden aceptar el tipo de registro" .

Dar el parámetro del tipo things no funciona cuando la función se llama como insert_thing(NEW) : "function insert_thing (flavored_view) does not exist" .

El casting simple no parece estar disponible aquí; insert_thing(NEW::things) produce "no se puede insert_thing(NEW::things) tipo de estilo con sabor a cosas" . Escribir una función CAST para cada vista eliminaría lo que ganamos mediante el uso de una función auxiliar.

¿Algunas ideas?

Answer 1

Básicamente puede convertir un registro en una variable hstore y pasar la variable hstore en lugar de una variable de registro a una función. Usted convierte el registro en hstore, es decir:

DECLARE r record; h hstore;
h = hstore(r);

Su función de ayuda también debe cambiarse así:

CREATE FUNCTION insert_thing (new_thing hstore) RETURNS INTEGER AS $fun$
DECLARE
    inserted_id  INT;
BEGIN
    INSERT INTO things (name) VALUES (
        new_thing -> ''name''
        -- (plus 30 more columns)
    ) RETURNING id INTO inserted_id;
    RETURN inserted_id;
END;
$fun$ LANGUAGE plpgsql;

Y la llamada:

inserted_id = insert_thing(hstore(NEW));

Espero eso ayude

Answer 2

Hay varias opciones, dependiendo de la imagen completa.
Básicamente, su función de inserción podría funcionar así:

CREATE FUNCTION insert_thing (_thing flavored_view)
   RETURNS int AS
$func$
   INSERT INTO things (name) VALUES ($1.name) -- plus 30 more columns
   RETURNING id;
$func$ LANGUAGE sql;

Usando el tipo de fila de la vista , porque NEW en su desencadenador es de este tipo.
Use una función SQL simple, que puede estar en línea y funcionar mejor.

Llamada demo:

SELECT insert_thing(''(1, foo, 1, bar)'');

Dentro de tu disparador flavored_trig () :

inserted_id := insert_thing(NEW);

O, básicamente, reescrito:

IF TG_OP = ''INSERT'' THEN
   INSERT INTO flavored_things(thing_id, flavor)
   VALUES (insert_thing(NEW), NEW.flavor);
   RETURN NEW;
ELSIF ...

record no es un tipo válido fuera de PL / pgSQL, es solo un marcador de posición genérico para un tipo de fila aún desconocido en PL / pgSQL) por lo que no puede usarlo para un parámetro de entrada en una declaración de función.

Para una función más dinámica que acepte varios tipos de filas , puede usar un tipo polimórfico . Ejemplos:

• Cómo devolver una tabla por rowtype en PL / pgSQL
• Refactorizar una función PL / pgSQL para devolver el resultado de varias consultas SELECT
• ¿Cómo escribir una función que devuelva valores enteros o de texto?

Answer 3

Tipos compuestos PostgresSQL tiene documentación sobre esto, esencialmente necesitas usar algo como

''()'' o ROW () para construir el tipo compuesto para que una fila pase a una función.