Spaltenname in Zusammenhang mit OLD. bzw. NEW. in Trigger nicht auflösbar

Hallo!

Habe folgendes Problem mit einem History-Trigger, welcher mir den alten und den neuen Wert der aktualisierten Spalten in eine extra dafür vorgesehene Tabelle (“historytabelle”) schreiben soll.
Mein Problem dabei ist, dass ich die jeweiligen Spalten per SQL-Statement erst beim Ausführen des Triggers zur Laufzeit aus den Systemtabellen hole und dann in der Schleife vergleiche, ob der alte Wert != dem neuen Wert.
Den Trigger habe ich in plpgsql geschrieben.
Er wird jeweils beim UPDATE auf die jeweilige Tabelle ausgeführt.


Aufbau der History-Tabelle, in die nur geänderten Spalten mit je einem Datensatz eingefügt werden sollen:

tabelle character varying
spalte character varying
wert_alt character varying
wert_neu character varying
zeit timestamp



Inhalt meines History-Triggers:

declare
spalten record;
spaltenname varchar;
wert_old varchar;
wert_new varchar;

BEGIN

for spalten in SELECT spaltenname as sn ROM systemtabelle
loop

spaltenname := spalten.sn;
wert_old := OLD.spaltenname;
wert_new := NEW.spaltenname;

IF (wert_new != wert_old) THEN
insert into historytabelle
(tabelle, spalte, wert_alt, wert_neu, zeit)
values
(‘Tabellenname’, spaltenname, wert_old, wert_new, current_timestamp);
END IF;

END loop;
RETURN new;
END;




Beim Ausführen dieses Triggers erhalte ich immer folgende Fehlermeldung, da der Inhalt von “wert_old” bzw. “wert_new” nicht ausgewertet werden kann & somit nach einer Spalte “spaltenname” in der jeweiligen Tabelle gesucht wird.

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: record “old” has no field “spaltenname” CONTEXT: PL/pgSQL function “hallo” line 17 at assignment in … .



Wenn ich im Trigger knall hart ne Spalte eintrage, die ich auch per Schleife zurück bekomme, läuft er ohne Fehler sauber durch. Ich habe, wie gesagt, halt “NUR” das Problem, dass ich hinter OLD. bzw. NEW. die Variable spaltenname nicht sauber aufgelöst werden kann.
Und das Zusammenkonkatinieren wert_old := OLD.spalten.sn; geht ja nunmal leider nicht…

wert_old := OLD.status;
wert_new := NEW.status;




Hat jemand eine Idee?

Vielen Dank.
easyrider1203

Du willst dynamisch generierte SQL-Befehle ausführen, also executieren, also EXECUTE. Reicht das als Wink?

Andreas

Ich glaub, jetzt haben wir uns falsch verstanden. Das mit dem Ausführen der SQL-Befehle klapp ja alles. Ich habe halt das Problem, dass der Inhalt aus spaltenname (Bsp.: spalten.sn -> status) nicht richtig mit OLD. & NEW. aufgelöst werden kann, sodass ich, wie im Beispiel, den alten sowie den neuen Inhalt aus der Spalte “status” erhalte.

wert_old := OLD.spaltenname;
wert_new := NEW.spaltenname;

Er sucht halt nach einer Spalte spaltename, obwohl er hierbei den Inhalt aus spaltenname “dranhängen” soll -> OLD.status


In PHP würde ich mein Problem so beschreiben:
$test=“hallo”; // In Variable $test wird Text “hallo” gespeichert
$$test=…; // Der Inhalt aus $test wird als neue Variable deklariert,
sodass daraus Variable $hallo entsteht.

So ungefähr kann man das miteinander vergleichen…

Ja.

Das mit dem Ausführen der SQL-Befehle klapp ja alles.

Nein. Deswegen fragst Du ja auch.

Ich habe halt das Problem, dass der Inhalt aus > spaltenname > (Bsp.: spalten.sn -> status) nicht richtig mit OLD. & NEW. aufgelöst werden kann, sodass ich, wie im Beispiel, den alten sowie den neuen Inhalt aus der Spalte “status” erhalte.

wert_old := OLD.spaltenname;
wert_new := NEW.spaltenname;

Und exakt das ist flasch.

Er sucht halt nach einer Spalte spaltename, obwohl er hierbei den Inhalt aus spaltenname “dranhängen” soll -> OLD.status

Genau. Und Du suchst
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Andreas

Is nich der erste Trigger, den ich schreibe, aber irgendwie habe ich mich echt festgebissen :angry:

Es ist hier egal, ob ich meine vorherige Schreibweise nehme oder das INSERT zusammenbaue und dann mit Execute ausführe. Ich benötige dennoch eine Variable in der meine gewünschten Inhalte stehen. Schließlich brauche ich um mein Statement noch das IF, welches prüft, dass der neue != dem alten Wert ist - sonst soll´s doch nicht ausgeführt werden.

IF (wert_new != wert_old) THEN
insert into …
END IF;


Wenn ich das Statement erst zusammenbastele und dann per EXECUTE ausführe, erhalte ich immer folgende Meldung:

Warning: pg_exec() [function.pg-exec]: Query failed: ERROR: NEW used in query that is not in a rule CONTEXT: SQL statement “insert into historytabelle (tabelle, spalte, wert_neu, wert_alt, zeit) values (K1, status,NEW.status,OLD.status,2007-03-16 14:10:58.529901+00);” PL/pgSQL function “hallo” line 25 at execute statement in …

… und er hat Recht, darum muß ja vorher die jeweilige Variable schon ausgewertet sein, dass er hier den INHALT nimmt und nicht, wie dargestellt, meckert.

Is alles nen bißchen kompliziert, aber ich hoffe, irgendeiner versteht, was ich meine…

Mag sein. Du bringst hier aber nun auch offensichtlich ein neues Problem. Egal. Ich zeigt Dir jetzt ein Beispiel,
was offensichtlich das macht, was Du machen willst mit Ausnahme, daß ich NICHT versuche, einen universellen
Trigger für alle möglichen Tabellen und Spaltennamen zu schreiben sondern für eine gegebene Tabelle und eine dafür passende History-Tabelle. Falls Du das aufbohren willst, so daß es universell funktioniert, wirst Du EXECUTE
brauchen, weil Du die Spaltennamen Dir erst irgendwo zusammensuchen mußt.

-- zuerst mal die Tabellen

test=# create table foo (id serial primary key, ts timestamp default now(), wert int);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
Time: 539.875 ms
test=*# commit;
COMMIT
Time: 8.329 ms
test=# create table foo_hist (id serial primary key, key int references foo, ts timestamp default now(), alt int, neu int);
NOTICE:  CREATE TABLE will create implicit sequence "foo_hist_id_seq" for serial column "foo_hist.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_hist_pkey" for table "foo_hist"
CREATE TABLE
Time: 250.524 ms
test=*# commit;
COMMIT
Time: 11.502 ms

-- nun den Trigger
-- das habe ich in einer extra Datei, welche ich mit \i eingelesen habe

create or replace function fhist() returns trigger as $$
begin
        if NEW.wert != OLD.wert then
                insert into foo_hist(key, alt, neu) values (NEW.id, OLD.wert, NEW.wert);
        end if;
        return NEW;
end;
$$ language plpgsql;

create trigger trg_foo before update on foo for each row execute procedure fhist();

-- so:
test=*# insert into foo (wert) values (1);
INSERT 0 1
Time: 46.070 ms
test=*# insert into foo (wert) values (2);
INSERT 0 1
Time: 1.648 ms
test=*# commit;
COMMIT
Time: 2.904 ms
test=# select * from foo;
 id |             ts             | wert
----+----------------------------+------
  1 | 2007-03-16 15:26:14.061437 |    1
  2 | 2007-03-16 15:26:14.061437 |    2
(2 rows)

Time: 0.916 ms
test=*# select * from foo_hist;
 id | key | ts | alt | neu
----+-----+----+-----+-----
(0 rows)

Time: 1.913 ms

-- hier war noch mehr, u.a. ein commit

test=*# update foo set wert=10 where id = 1;
UPDATE 1
Time: 53.628 ms
test=*# select * from foo;
 id |             ts             | wert
----+----------------------------+------
  2 | 2007-03-16 15:26:14.061437 |    2
  1 | 2007-03-16 15:26:14.061437 |   10
(2 rows)

Time: 1.119 ms
test=*# select * from foo_hist;
 id | key |             ts             | alt | neu
----+-----+----------------------------+-----+-----
  1 |   1 | 2007-03-16 15:26:36.489166 |   1 |  10
(1 row)

Wie gesagt, hier sind Spaltennamen etc. bekannt. Der Aufwand, immer erst im Katalog zu schauen, welche Tabelle nun betroffen ist, welche Spalten da sind etc. ist nicht unerheblich. Brauchst/willst Du das wirklich?
Ich hab jetzt grad wenig Zeit&Lust, sowas zu frickeln.

Ach ja: falls Du Deinen Code weitermachen willst, wäre es vielleicht zielführend, ihn hier zu zeigen.

Btw.: ads hat ein Paket ‘tablellog’ geschrieben, auf pgfoundry zu finden. Das realisert Dir eine History-Tabelle für jede gewünschte Tabelle, mit der Du auch einen Zustand zu einem bestimmten Zeitpunkt wieder herstellen kannst. Vielleicht ist das schon eine für Dich passende Lösung.

Andreas

Danke für die, wieder mal, schnelle Antwort.
Ich werde mich mal durchfrickeln :slight_smile:

INFO: Meines Wissens - nicht.
Wenn ich den Inhalt, wie von Anfang an meinerseits geplant bloß bis jetzt noch nicht richtig umgesetzt, nämlich in meine dargestellten Variablen wert_neu und wert_alt bekomme, habe ich somit beide Fliegen mit einer Klappe geschlagen. Ich kann vernünftig in der IF-Anweisung prüfen und mein INSERT sollte auch funktionieren…

Aber wie gesagt, werde mich da erstma durchfrickeln :slight_smile: