Zusammenführen einer Tabelle und eines Änderungsprotokolls in einer Ansicht in PostgreSQL

Lesezeit: 8 Minuten

Zusammenfuhren einer Tabelle und eines Anderungsprotokolls in einer Ansicht in
Vadym

Meine PostgreSQL-Datenbank enthält eine Tabelle zum Speichern von Instanzen einer registrierten Entität. Diese Tabelle wird per Tabellen-Upload ausgefüllt. Eine Webschnittstelle ermöglicht es einem Bediener, die präsentierten Informationen zu modifizieren. Die Originaldaten werden jedoch nicht verändert. Alle Änderungen werden in einer separaten Tabelle gespeichert changes mit den Spalten unique_id, column_name, value und updated_at.

Sobald Änderungen vorgenommen wurden, werden sie dem Operator angezeigt, indem zuerst die ursprüngliche Tabelle und dann die Änderungstabelle abgefragt wird (unter Verwendung der Instanz-ID und des letzten Änderungsdatums, gruppiert nach Spaltennamen). Die beiden Ergebnisse werden in PHP zusammengeführt und auf der Weboberfläche präsentiert. Dies ist eine ziemlich starre Vorgehensweise, und ich möchte die gesamte Logik in SQL belassen.

Mit der folgenden Abfrage kann ich ganz einfach die neuesten Änderungen für die Tabelle auswählen:

SELECT fltr_chg.unique_id, fltr_chg.column_name, chg_val.value 
FROM changes AS chg_val
JOIN ( 
      SELECT chg_rec.unique_id, chg_rec.column_name, MAX( chg_rec.updated_at )
      FROM information_schema.columns AS source
      JOIN changes AS chg_rec ON source.table_name="instances"
                             AND source.column_name = chg_rec.column_name
      GROUP BY chg_rec.unique_id, chg_rec.column_name
     ) AS fltr_chg ON fltr_chg.unique_id = chg_val.unique_id
                  AND fltr_chg.column_name = chg_val.column_name;

Und Auswahl der Einträge aus dem instances Tabelle ist genauso einfach:

SELECT * FROM instances;

Wenn es nun nur eine Möglichkeit gäbe, das erstere Ergebnis umzuwandeln und die resultierenden Werte durch das letztere zu ersetzen, basierend auf dem unique_id und column_name, und das Ergebnis weiterhin als Tabelle beizubehalten, wäre das Problem gelöst. Ist dies möglich?

Ich bin mir sicher, dass dies nicht das seltenste Problem ist und höchstwahrscheinlich verfolgen einige Systeme Änderungen an den Daten auf ähnliche Weise. Wie wenden sie sie auf die Daten zurück, wenn nicht auf eine der oben beschriebenen Weisen (aktuelle und gesuchte Lösungen)?

  • Was ist Ihre Version oder PostgreSQL?

    – Erwin Brandstetter

    11. Apr. ’12 um 16:48

  • Verzeihen Sie, ich habe den Kommentar nicht bemerkt. Ich verwende die Version 8.1. Trotzdem konnte ich das Modul contrib erfolgreich installieren und tablefunc.sql in mein Schema einführen.

    – Vadym

    12. Apr. ’12 um 18:57

  • 8.1 ist hoffnungslos veraltet. Ziehen Sie ein Upgrade auf eine neuere Version in Betracht.

    – Erwin Brandstetter

    13. Apr. ’12 um 11:12

  • Ja, ein Update auf 8.4 ist derzeit in Arbeit. Nur aufgrund der Starrheit der Bereitstellungsanforderungen müssen wir jedoch in der Nähe der End-of-Life-Versionen hinterherhinken.

    – Vadym

    13. Apr. ’12 um 19:26

  • Fast alles in meiner Lösung funktioniert mit v8.4. Aus dem Kopf, wie Sie zusätzliche Module installieren (das haben Sie bereits gelöst) und string_agg() sollten die einzigen Ausnahmen sein. Ersetze das durch array_to_string(array_agg(col1), ', ') im 8.4. Hier ist ein Beispiel.

    – Erwin Brandstetter

    13. Apr. ’12 um 19:37


1641742139 541 Zusammenfuhren einer Tabelle und eines Anderungsprotokolls in einer Ansicht in
Erwin Brandstetter

Vorausgesetzt, Postgres 9.1 oder später.
Ich habe Ihre grundlegende Abfrage vereinfacht / optimiert, um die neuesten Werte abzurufen:

SELECT DISTINCT ON (1,2)
       c.unique_id, a.attname AS col, c.value
FROM   pg_attribute a
LEFT   JOIN changes c ON c.column_name = a.attname
                     AND c.table_name="instances"
                 --  AND c.unique_id   = 3  -- uncomment to fetch single row
WHERE  a.attrelid = 'instances'::regclass   -- schema-qualify to be clear?
AND    a.attnum > 0                         -- no system columns
AND    NOT a.attisdropped                   -- no deleted columns
ORDER  BY 1, 2, c.updated_at DESC;

Ich frage den PostgreSQL-Katalog anstelle des Standard-Informationsschemas ab, weil das schneller ist. Beachten Sie die besondere Besetzung zu ::regclass.

Nun, das gibt Ihnen ein Tabelle. Du willst alle Werte für einen unique_id in einem Reihe.
Um dies zu erreichen, haben Sie grundsätzlich drei Möglichkeiten:

  1. Ein Subselect (oder Join) pro Spalte. Teuer und unhandlich. Aber eine gültige Option nur für einige Spalten.

  2. Ein großer CASE Erklärung.

  3. EIN Pivot-Funktion. PostgreSQL bietet die crosstab() Funktion im Zusatzmodul tablefunc dafür.
    Grundlegende Anweisungen:

    • PostgreSQL-Kreuztabellenabfrage

Basic Pivot-Tisch mit crosstab()

Ich habe die Funktion komplett umgeschrieben:

SELECT *
FROM   crosstab(
    $x$
    SELECT DISTINCT ON (1, 2)
           unique_id, column_name, value
    FROM   changes
    WHERE  table_name="instances"
 -- AND    unique_id = 3  -- un-comment to fetch single row
    ORDER  BY 1, 2, updated_at DESC;
    $x$,

    $y$
    SELECT attname
    FROM   pg_catalog.pg_attribute
    WHERE  attrelid = 'instances'::regclass  -- possibly schema-qualify table name
    AND    attnum > 0
    AND    NOT attisdropped
    AND    attname <> 'unique_id'
    ORDER  BY attnum
    $y$
    )
AS tbl (
 unique_id integer
-- !!! You have to list all columns in order here !!! --
);

Ich habe die Katalogsuche von der Wertabfrage getrennt, da die crosstab() Funktion mit zwei Parametern stellt Spaltennamen separat bereit. Fehlende Werte (kein Eintrag bei Änderungen) werden durch ersetzt NULL automatisch. Eine perfekte Ergänzung für diesen Anwendungsfall!

Vorausgesetzt, dass attname Streichhölzer column_name. Ausschließlich unique_id, die eine besondere Rolle spielt.

Volle Automatisierung

Adressierung Ihres Kommentars: Da ist ein Weg um die Spaltendefinitionsliste automatisch bereitzustellen. Es ist jedoch nichts für schwache Nerven.

Ich verwende hier eine Reihe von erweiterten Postgres-Funktionen: crosstab(), plpgsql-Funktion mit dynamischem SQL, Verarbeitung von zusammengesetzten Typen, erweiterte Dollarnotierungen, Katalogsuche, Aggregatfunktion, Fensterfunktion, Objektbezeichnertyp, …

Test Umgebung:

CREATE TABLE instances (
  unique_id int
, col1      text
, col2      text -- two columns are enough for the demo
);

INSERT INTO instances VALUES
  (1, 'foo1', 'bar1')
, (2, 'foo2', 'bar2')
, (3, 'foo3', 'bar3')
, (4, 'foo4', 'bar4');

CREATE TABLE changes (
  unique_id   int
, table_name  text
, column_name text
, value       text
, updated_at  timestamp
);

INSERT INTO changes VALUES
  (1, 'instances', 'col1', 'foo11', '2012-04-12 00:01')
, (1, 'instances', 'col1', 'foo12', '2012-04-12 00:02')
, (1, 'instances', 'col1', 'foo1x', '2012-04-12 00:03')
, (1, 'instances', 'col2', 'bar11', '2012-04-12 00:11')
, (1, 'instances', 'col2', 'bar17', '2012-04-12 00:12')
, (1, 'instances', 'col2', 'bar1x', '2012-04-12 00:13')

, (2, 'instances', 'col1', 'foo2x', '2012-04-12 00:01')
, (2, 'instances', 'col2', 'bar2x', '2012-04-12 00:13')

 -- NO change for col1 of row 3 - to test NULLs
, (3, 'instances', 'col2', 'bar3x', '2012-04-12 00:13');

 -- NO changes at all for row 4 - to test NULLs

Automatisierte Funktion für ein Tisch

CREATE OR REPLACE FUNCTION f_curr_instance(int, OUT t public.instances) AS
$func$
BEGIN
   EXECUTE $f$
   SELECT *
   FROM   crosstab($x$
      SELECT DISTINCT ON (1,2)
             unique_id, column_name, value
      FROM   changes
      WHERE  table_name="instances"
      AND    unique_id =  $f$ || $1 || $f$
      ORDER  BY 1, 2, updated_at DESC;
      $x$
    , $y$
      SELECT attname
      FROM   pg_catalog.pg_attribute
      WHERE  attrelid = 'public.instances'::regclass
      AND    attnum > 0
      AND    NOT attisdropped
      AND    attname <> 'unique_id'
      ORDER  BY attnum
      $y$) AS tbl ($f$
   || (SELECT string_agg(attname || ' ' || atttypid::regtype::text
                       , ', ' ORDER BY attnum) -- must be in order
       FROM   pg_catalog.pg_attribute
       WHERE  attrelid = 'public.instances'::regclass
       AND    attnum > 0
       AND    NOT attisdropped)
   || ')'
   INTO t;
END
$func$  LANGUAGE plpgsql;

Der Tisch instances ist fest codiert, das Schema ist als eindeutig qualifiziert. Beachten Sie die Verwendung des Tabellentyps als Rückgabetyp. Für jede Tabelle in PostgreSQL wird automatisch ein Zeilentyp registriert. Dies muss mit dem Rückgabetyp des übereinstimmen crosstab() Funktion.

Dadurch wird die Funktion an den Typ der Tabelle gebunden:

  • Sie erhalten eine Fehlermeldung, wenn Sie es versuchen DROP Der Tisch
  • Ihre Funktion wird nach einem fehlschlagen ALTER TABLE. Sie müssen es neu erstellen (ohne Änderungen). Ich halte das für einen Fehler in 9.1. ALTER TABLE sollte die Funktion nicht stillschweigend unterbrechen, sondern einen Fehler auslösen.

Dies funktioniert sehr gut.

Anruf:

SELECT * FROM f_curr_instance(3);

unique_id | col1  | col2
----------+-------+-----
 3        |<NULL> | bar3x

Beachten Sie, wie col1 ist NULL Hier.
In einer Abfrage verwenden, um eine Instanz mit ihren neuesten Werten anzuzeigen:

SELECT i.unique_id
     , COALESCE(c.col1, i.col1)
     , COALESCE(c.col2, i.col2)
FROM   instances i
LEFT   JOIN f_curr_instance(3) c USING (unique_id)
WHERE  i.unique_id = 3;

Vollautomatisierung für jeder Tisch

(Hinzugefügt 2016. Das ist Dynamit.)
Benötigt Postgres 9.1 oder später. (Könnte mit Seite 8.4 funktionieren, aber ich habe mich nicht die Mühe gemacht, einen Backpatch durchzuführen.)

CREATE OR REPLACE FUNCTION f_curr_instance(_id int, INOUT _t ANYELEMENT) AS
$func$
DECLARE
   _type text := pg_typeof(_t);
BEGIN
   EXECUTE
   (
   SELECT format
         ($f$
         SELECT *
         FROM   crosstab(
            $x$
            SELECT DISTINCT ON (1,2)
                   unique_id, column_name, value
            FROM   changes
            WHERE  table_name = %1$L
            AND    unique_id  = %2$s
            ORDER  BY 1, 2, updated_at DESC;
            $x$    
          , $y$
            SELECT attname
            FROM   pg_catalog.pg_attribute
            WHERE  attrelid = %1$L::regclass
            AND    attnum > 0
            AND    NOT attisdropped
            AND    attname <> 'unique_id'
            ORDER  BY attnum
            $y$) AS ct (%3$s)
         $f$
          , _type, _id
          , string_agg(attname || ' ' || atttypid::regtype::text
                     , ', ' ORDER BY attnum)  -- must be in order
         )
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = _type::regclass
   AND    attnum > 0
   AND    NOT attisdropped
   )
   INTO _t;
END
$func$  LANGUAGE plpgsql;

Anruf (bei Angabe des Tabellentyps mit NULL::public.instances:

SELECT * FROM f_curr_instance(3, NULL::public.instances);

Verwandt:

  • Refaktorieren Sie eine PL/pgSQL-Funktion, um die Ausgabe verschiedener SELECT-Abfragen zurückzugeben
  • So legen Sie den Wert eines zusammengesetzten Variablenfelds mit dynamischem SQL fest

  • Super, danke für die Antwort. Ich antworte, sobald ich es getestet habe. Schade, dass es an der von Ihnen erwähnten Stelle keine Möglichkeit gibt, eine SELECT-Anweisung anzugeben — !!! Sie müssen hier alle Spalten auflisten !!! –.

    – Vadym

    11. Apr. ’12 um 21:02

.

221440cookie-checkZusammenführen einer Tabelle und eines Änderungsprotokolls in einer Ansicht in PostgreSQL

This website is using cookies to improve the user-friendliness. You agree by using the website further.

Privacy policy