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)?
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:
-
Ein Subselect (oder Join) pro Spalte. Teuer und unhandlich. Aber eine gültige Option nur für einige Spalten.
-
Ein großer CASE
Erklärung.
-
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
.
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 durcharray_to_string(array_agg(col1), ', ')
im 8.4. Hier ist ein Beispiel.– Erwin Brandstetter
13. Apr. ’12 um 19:37