PL/pgSQL: Daten aus NEW.Spalte mit variablem Namen auslesen

[Tom.S]

Hallo,
ich möchte aus einer OLD/NEW-Spalte einen Wert auslesen. Nun stehe ich vor dem Problem, dass meine id-Spalten wie alle meine Spalten ein Präfix haben, das tabellenspezifisch ist. Beispiel utn_id für die Tabelle utn_unternehmen oder org_id für org_organisation. Eine Funktion soll mir jetzt unabhängig von der Tabelle die aktuelle id in eine Archiv-Tabelle schreiben. Hätte ich jede id einheitlich als 'id' benannt, wäre mit NEW.id alles in Butter.
Habe ich aber nicht und deshalb muss ich einen Weg finden, den id-Wert aus NEW herauszubekommen, ohne den Tabellennamen schon hardkodiert in die Funktion zu schreiben.

Möglichkeit 1: Es gibt wie, glaube ich, bei MySQL eine Möglichkeit auf die erste Spalte zuzugreifen, so in der Art NEW.[1].
Geht meines Wissens nicht.

Möglichkeit 2: Ich ermittle aus dem Tabellennamen, der ja zugreifbar ist, den Namen der id-Spalte. Das könnte gehen, den Namen utn_id in eine Variable zu bugsieren, ist keine Schwierigkeit. Irgendwie schaffe ich es aber nicht, NEW und die Variable zu koppeln: NEW.var funktioniert nicht, aber vielleicht fehlt mir hier nur die genaue Syntax.

Möglichkeit 3: Man kann die Werte von NEW.* irgendwie in eine Array-Struktur überführen und dann auf den ersten Eintrag zugreifen. Könnte gehen, habe dazu aber nichts gefunden.

Möglichkeit 4: Man übergibt der Triggerfunktion einen passenden Parameter. Würde gehen, lieber wäre mir aber eine der ersten Varianten.

Möglichkeit 5: Die würde gehen, möchte ich aber aus Performancegründen (es handelt sich um eine Archiv-Tabelle, die alles protokolliert) und weil es nicht sonderlich schön ist vermeiden: Man holt sich mit einem zusätzlichen SELECT den Namen der id-Spalte anhand des Tabellennamens aus dem information_schema.

 

[akretschmer]

2. wäre wahrscheinlich die einfachste Lösung. Du baust Dir dynamisch das SQL zusammen und nutzt dann EXECUTE. Dynamisches SQL halt, siehe Doku.

 

[akretschmer]

Hier hast Du mal noch 2 andere Lösungen für ähnliche Probleme:

http://amor.cms.hu-berlin.de/~kuner...g-funktion-in-plpgsql-oder-i-love-postgresql/
http://www.postgresonline.com/journal/archives/330-Using-HStore-for-Archiving.html

 

[Tom.S]

Merci, es funktioniert.

Ich habe es mit EXECUTE geschafft, die generierte Variable mit dem Spaltenname an NEW anzuhängen:

DECLARE
    id_var              text;
BEGIN
    id_var:= substring(TG_TABLE_NAME from 1 for 3) || '_id';

     EXECUTE 'INSERT INTO metadaten.arc_archive(arc_row_id)
              VALUES ($1.' || quote_ident(id_var) || ')'
              USING  OLD;
     RETURN OLD;
END;

und damit habe ich meine Archiv-Funktion fertig.

Vielleicht ist es für jemanden noch hilfreich, deshalb poste ich sie hier. Sie baut u. a. auf den von @akretschmer verlinkten Tutorials auf.

Ich speichere in der Archiv-Tabelle
- Schemaname
- Tabellenname
- Primärschlüssel-Wert des behandelten Datensatzes
- Ob es sich um (I)nsert, (U)pdate oder (D)elete handelt
- Transaktions-ID der Operation (Für Fälle, in denen mehre Tabellen betroffen sind.)
- DB-User
- Timestamp
- den kompletten Datensatz in einem hstore-Feld

DECLARE
    id_var              text;
    tsa_id       bigint;
BEGIN
    id_var:= substring(TG_TABLE_NAME from 1 for 3) || '_id';
    tsa_id := txid_current();

    IF(TG_OP = 'DELETE') THEN
          EXECUTE 'INSERT INTO metadaten.arc_archive(arc_schema_name,  arc_table_name, arc_row_id, arc_dml_event, arc_transaktion_id, arc_user, arc_data)
                 VALUES ($1, $2, $3.' || quote_ident(id_var) || ',''D'', $4, $5, $6)'
                 USING TG_TABLE_SCHEMA, TG_TABLE_NAME, OLD, tsa_id, user, hstore(OLD);
          RETURN OLD;
     ELSIF(TG_OP = 'UPDATE') THEN
          EXECUTE 'INSERT INTO metadaten.arc_archive(arc_schema_name,  arc_table_name, arc_row_id, arc_dml_event, arc_transaktion_id, arc_user, arc_data)
                 VALUES ($1, $2, $3.' || quote_ident(id_var) || ',''U'', $4, $5, $6)'
                 USING TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW, tsa_id, user, hstore(NEW);
          RETURN NEW;
   ELSIF(TG_OP = 'INSERT') THEN
          EXECUTE 'INSERT INTO metadaten.arc_archive(arc_schema_name,  arc_table_name, arc_row_id, arc_dml_event, arc_transaktion_id, arc_user, arc_data)
                 VALUES ($1, $2, $3.' || quote_ident(id_var) || ',''I'', $4, $5, $6)'
                 USING TG_TABLE_SCHEMA, TG_TABLE_NAME, NEW, tsa_id, user, hstore(NEW);     
          RETURN NEW;
  
    END IF;
    RETURN NULL;
END;

Damit braucht es nur noch in jeder überwachten Tabelle einen After-Trigger, der 'for each row' und für Insert, Delete und Update die Trigger-Funktion aufruft:

CREATE TRIGGER archive_trigger
  AFTER INSERT OR UPDATE OR DELETE
  ON organisation.org_organisation
  FOR EACH ROW
  EXECUTE PROCEDURE metadaten.archive_dml_data();

 

[Distrilec]

Ich würde ja gerne "Gefällt mir" drücken... Aber dieser Mix aus Bind-Variable und Stringverkettung...

 

[Tom.S]

Du darfst sehr gerne eine (funktionierende) Verbesserung vorschlagen.