Comment utiliser dynamiquement le nom de la TABLE TG dans PostgreSQL 8.2?

j'essaie d'écrire une fonction de déclenchement dans PostgreSQL 8.2 qui utilisera dynamiquement TG_TABLE_NAME pour générer et exécuter une instruction SQL. Je peux trouver toutes sortes d'exemples pour les versions ultérieures de PostgreSQL, mais je suis coincé sur 8.2 en raison de certaines exigences. Voici ma fonction actuelle qui fonctionne, mais c'est à peine dynamique:

CREATE OR REPLACE FUNCTION cdc_TABLENAME_function() RETURNS trigger AS $cdc_function$
        DECLARE 
        op  cdc_operation_enum;
    BEGIN
        op = TG_OP;

        IF (TG_WHEN = 'BEFORE') THEN
            IF (TG_OP = 'UPDATE') THEN
                op = 'UPDATE_BEFORE';
            END IF;

            INSERT INTO cdc_test VALUES (DEFAULT,DEFAULT,op,DEFAULT,DEFAULT,OLD.*); 
        ELSE
            IF (TG_OP = 'UPDATE') THEN
                op = 'UPDATE_AFTER';
            END IF;

            INSERT INTO cdc_test VALUES (DEFAULT,DEFAULT,op,DEFAULT,DEFAULT,NEW.*); 
        END IF;

        IF (TG_OP = 'DELETE') THEN
            RETURN OLD;
        ELSE
            RETURN NEW;
        END IF;
    END;

de la façon dont cela est écrit actuellement, je devrais écrire une fonction de déclenchement séparée pour chaque table. Je voudrais comme pour utiliser TG_TABLE_NAME pour construire dynamiquement ma déclaration INSERT et juste la préfixer avec' cdc_ ' puisque toutes les tables suivent la même convention de nommage. Alors je peux avoir chaque déclencheur pour chaque appel de table juste une fonction.

5
demandé sur Erwin Brandstetter 2011-09-22 21:38:00

2 réponses

je cherchais exactement la même chose il y a quelques années. Une fonction de déclenchement pour les gouverner tous! J'ai demandé sur les listes usenet, essayé diverses approches, en vain. Le consensus sur la question était cela ne pouvait pas être fait . Une lacune de PostgreSQL 8.3 ou plus.

depuis PostgreSQL 8.4 vous pouvez juste:

EXECUTE 'INSERT INTO ' || TG_RELID::regclass::text || ' SELECT ().*'
USING NEW;

avec pg 8.2 vous avez un problème:

  • ne peut accéder dynamiquement aux colonnes de NEW / OLD . Vous avez besoin de savoir les noms de colonne au moment de l'écriture de la fonction de déclenchement.
  • NEW / OLD ne sont pas visibles à l'intérieur de EXECUTE .
  • EXECUTE .. USING pas encore né.

il y a un truc, cependant.

Chaque nom de table dans le système peut servir type composite du même nom. Par conséquent, vous pouvez créer une fonction qui prend NEW / OLD comme paramètre, et l'exécuter. Vous pouvez dynamiquement créer et détruire cette fonction sur chaque événement déclencheur:

fonction de déclenchement:

CREATE OR REPLACE FUNCTION trg_cdc()
  RETURNS trigger AS
$func$
DECLARE
   op      text := TG_OP || '_' || TG_WHEN;
   tbl     text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident(TG_TABLE_NAME);
   cdc_tbl text := quote_ident(TG_TABLE_SCHEMA) || '.'
                || quote_ident('cdc_' || TG_TABLE_NAME);
BEGIN

EXECUTE 'CREATE FUNCTION f_cdc(n ' || tbl || ', op text)
  RETURNS void AS $x$ BEGIN
  INSERT INTO ' || cdc_tbl || ' SELECT op, (n).*;
END $x$ LANGUAGE plpgsql';

CASE TG_OP
WHEN 'INSERT', 'UPDATE' THEN
   PERFORM f_cdc(NEW, op);
WHEN 'DELETE' THEN
   PERFORM f_cdc(OLD, op);
ELSE
   RAISE EXCEPTION 'Unknown TG_OP: "%". Should not occur!', TG_OP;
END CASE;

EXECUTE 'DROP FUNCTION f_cdc(' || tbl || ', text)';

IF TG_OP = 'DELETE' THEN
    RETURN OLD;
ELSE
    RETURN NEW;
END IF;

END
$func$  LANGUAGE plpgsql;

Trigger:

CREATE TRIGGER cdc
BEFORE INSERT OR UPDATE OR DELETE ON my_tbl
FOR EACH ROW EXECUTE PROCEDURE trg_cdc();
Les noms de tables

doivent être traités comme des entrées utilisateur. Utilisez quote_ident() pour se défendre contre L'injection SQL.

cependant , de cette façon vous créez et supprimez une fonction pour chaque événement déclencheur simple. C'est une sacrée charge, Je ne dirais pas ça. Vous aurez à aspirer certaines tables de catalogue beaucoup.

terrain d'entente

PostgreSQL supporte "1519520920 la fonction de" surcharge . Par conséquent, une fonction par Tableau du même nom de base (mais un type de paramètre différent) peut coexister. Vous pourriez prendre l' Middle ground et réduire considérablement le bruit en créant f_cdc(..) une fois par table en même temps que vous créez la gâchette. C'est une fonction minuscule par table. Vous devez observer les changements des définitions de table, mais les tables ne devraient pas changer que souvent. Supprimer CREATE et DROP FUNCTION de la fonction de déclenchement, en arrivant à un petit déclencheur rapide et élégant.

je me voyais bien faire ça à la page 8.2. Sauf que je ne me vois pas faire tout à la page 8.2. Il a atteint fin de vie en décembre 2011 . Peut-être que tu peux améliorer quelque chose après tout.

13
répondu Erwin Brandstetter 2015-03-31 00:19:32

j'ai posé une question similaire il y a quelques années.

regardez cette question et voyez si elle vous donne des idées utiles:

insertion nouvelle.* à partir d'un générique de déclenchement à l'aide de l'EXÉCUTER dans PL/pgsql

0
répondu Adrian Pronk 2017-05-23 12:07:46