Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

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

Dieses Thema im Forum "PostgreSQL" wurde erstellt von Tom.S, 10 Juli 2015.

  1. Tom.S

    Tom.S Fleissiger Benutzer

    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.
     
  2. akretschmer

    akretschmer Datenbank-Guru

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

    akretschmer Datenbank-Guru

  4. Tom.S

    Tom.S Fleissiger Benutzer

    Merci, es funktioniert.

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

    Code:
    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

    Code:
    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:

    Code:
    CREATE TRIGGER archive_trigger
      AFTER INSERT OR UPDATE OR DELETE
      ON organisation.org_organisation
      FOR EACH ROW
      EXECUTE PROCEDURE metadaten.archive_dml_data();
    
     
    akretschmer gefällt das.
  5. Distrilec

    Distrilec Datenbank-Guru

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

    Tom.S Fleissiger Benutzer

    Du darfst sehr gerne eine (funktionierende) Verbesserung vorschlagen.
     
Die Seite wird geladen...
Ähnliche Themen - pgSQL Daten Spalte
  1. Günter Hecht
    Antworten:
    9
    Aufrufe:
    744
  2. ukulele
    Antworten:
    11
    Aufrufe:
    1.830
  3. ukulele
    Antworten:
    20
    Aufrufe:
    5.918
  4. foerster7891
    Antworten:
    1
    Aufrufe:
    3.465
  5. MarcoPolo
    Antworten:
    7
    Aufrufe:
    4.389

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden