wake-up-neo.net

INSERT mit dynamischem Tabellennamen in der Triggerfunktion

Ich bin nicht sicher, wie ich Folgendes erreichen kann:

CREATE OR REPLACE FUNCTION fnJobQueueBEFORE() RETURNS trigger AS $$
    DECLARE
        shadowname varchar := TG_TABLE_NAME || 'shadow';
    BEGIN
        INSERT INTO shadowname VALUES(OLD.*);
        RETURN OLD;
    END;
$$
LANGUAGE plpgsql;

Das heißt Einfügen von Werten in eine Tabelle mit einem dynamisch generierten Namen.
Wenn Sie den obigen Code ausführen, erhalten Sie:

ERROR:  relation "shadowname" does not exist
LINE 1: INSERT INTO shadowname VALUES(OLD.*)

Es scheint, dass Variablen nicht als Tabellennamen erweitert/erlaubt sind. Ich habe im Postgres-Handbuch keinen Hinweis darauf gefunden.

Ich habe bereits mit EXECUTE experimentiert:

  EXECUTE 'INSERT INTO ' || quote_ident(shadowname) || ' VALUES ' || OLD.*;

Aber kein glück:

ERROR:  syntax error at or near ","
LINE 1: INSERT INTO personenshadow VALUES (1,sven,,,)

Der RECORD-Typ scheint verloren zu sein: OLD.* scheint in eine Zeichenfolge konvertiert zu werden und wird neu sortiert, was zu allen möglichen Typproblemen führt (z. B. NULL-Werte).

Irgendwelche Ideen?

postgresqltriggersdynamic-sqlplpgsql
27
sschober

PostgreSQL 9.1 oder höher

format() verfügt über eine integrierte Möglichkeit, Kennungen zu umgehen. Einfacher als zuvor:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
   EXECUTE format('INSERT INTO %I.%I SELECT $1.*'
                , TG_TABLE_SCHEMA, TG_TABLE_NAME || 'shadow')
   USING OLD;

   RETURN OLD;
END
$func$  LANGUAGE plpgsql;

SQL-Geige.
Funktioniert auch mit einem VALUES -Ausdruck.

Hauptpunkte

  • Verwenden Sie format () oder quote_ident() , um ggf. Bezeichner anzugeben und gegen SQL-Injection zu schützen.
    Dies ist notwendig, auch mit Ihren eigenen Tabellennamen!
  • Schema-qualifizieren Sie den Tabellennamen. Abhängig von der aktuellen Einstellung von search_path kann ein bare Tabellenname ansonsten in eine andere Tabelle mit demselben Namen in einem anderen Schema aufgelöst werden.
  • Verwenden SieEXECUTEfür dynamische DDL-Anweisungen.
  • Übergeben Sie Werte sicher mit derUSING-Klausel.
  • Lesen Sie das Fine-Handbuch zu Ausführen dynamischer Befehle in plpgsql .
  • Beachten Sie, dassRETURN OLD; in der Triggerfunktion für einen TriggerBEFORE DELETEerforderlich ist. Details hier im Handbuch.

Sie erhalten die Fehlermeldung in Ihrer fast erfolgreichen Version, weil OLD nicht sichtbar ist in EXECUTE. Wenn Sie einzelne Werte der zerlegten Zeile wie gewünscht verketten möchten, müssen Sie die Textdarstellung jeder einzelnen Spalte mit quote_literal() vorbereiten, um eine gültige Syntax zu gewährleisten. Sie müssten auch vorher know Spaltennamen verwenden, um sie zu bearbeiten oder die Systemkataloge abzufragen - was Ihrer Vorstellung von einer einfachen, dynamischen Triggerfunktion entgegensteht.

Meine Lösung vermeidet all diese Komplikationen. Auch etwas vereinfacht.

PostgreSQL 9.0 oder früher

format() ist noch nicht verfügbar, also:

CREATE OR REPLACE FUNCTION foo_before()
  RETURNS trigger AS
$func$
BEGIN
    EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_SCHEMA)
                    || '.' || quote_ident(TG_TABLE_NAME || 'shadow')
                    || ' SELECT $1.*'
    USING OLD;

    RETURN OLD;
END
$func$  LANGUAGE plpgsql;

Verbunden:

49
Erwin Brandstetter

Ich bin gerade darüber gestolpert, weil ich nach einem dynamischen INSTEAD OF DELETE-Auslöser gesucht habe. Als Dankeschön für die Frage und die Antworten werde ich meine Lösung für Postgres 9.3 veröffentlichen.

CREATE OR REPLACE FUNCTION set_deleted_instead_of_delete()
RETURNS TRIGGER AS $$
BEGIN
    EXECUTE format('UPDATE %I set deleted = now() WHERE id = $1.id', TG_TABLE_NAME)
    USING OLD;
    RETURN NULL;
END;
$$ language plpgsql;
1
robkorv