Postgresql PLpg/SQL NEW,OLD ertekek elerese (megkerülve)

Adatbázis: SQL, XML DB

Sziasztok!

Egy olyan triggert szeretnek írni PLpgSQL-ben ami bármilyen táblához kötve UPDATE esetén log-olni tudja a változtatás idejét, táblanevét, oszlopát, régi, illetve új értékét.
Ez nyilván egy UPDATE és FOR EACH ROW trigger lenne.

Ehhez a NEW.valami és OLD.valami értékeket hasonlítom(nám) össze a triggeren belül.
Mivel több, különböző szerkezetü táblára is használnám a táblák szerkezetet a rendszer-táblákból olvastam ki, ez is működik is. (pg_class és meg 1-2 táblából egy select-el lekérdezhetők az oszlopnevek)

Viszont a gond ott van hogy hiába vannak meg az oszlopjaim nevei a NEW.xxx -valtozo értéket nem tudom elérni, mivel így csak sztringeket tudok előállítani pl: "NEW.id" !
Nincs valami mód, függvény akármi ami pl "NEW.date" sztring alapján a NEW.date változó tartalmat adja vissza? Vagy esetleg hogy lehetne ezt a problémát megkerülni.

EXECUTE -os játékok nem működtek, mert a lekérdezésben (úgy tűnik) a NEW.akarmi nem értelmezett változó. Valószinüleg csak a triggerben él, és az abból indított lekérdezésben nem, vagy csak az érték helyettesítődik át.
Működik:

b := 'id';
q := 'SELECT tabla.' || b || ' FROM tabla LIMIT 1;';
EXECUTE q INTO a;

De ha már:

b := 'id';
q := 'SELECT NEW.' || b || ';';
EXECUTE q INTO a;

hasznalok akkor:

ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "SELECT NEW.id;"
PL/pgSQL function "inner_logger" line 9 at EXECUTE statement

lesz az eredmény.

Valakinek valami ötlete?

  • 1425 megtekintés

Kosz, ezt meg nem olvastam. De sajnos a problemara ok sem tudtak megoldast talani. A sejtesem beigazolodott a NEW.xxx valtozok nem erhetoek el sql szinten :(
Az altalad linkelt resz inkabb azt erositene meg (en ertelmezesem szerint) hogy ezt plgpsql alatt nem lehet megoldani.
Lehet C triggert kell ra irni...
Szivas.

Semmi gond, en kiprobalom ha van eletkepes 5let. :-)

De sajnos az amit a 3. ban emlitettek nekem nem jo.
Ott a myt() fuggveny letrehozasakor a new.* -t hasznalja. Ami az osszes uj erteket jelenti
De nekem darabonkent ossze kellene hasonlitanom a
OLD.macska = NEW.macska
OLD.kutya = NEW.kutya
... stb stb mezoket.
Amit ott irtak arra jo ha mindent le akarnak menteni fuggetlenul attol hogy valtozott-e. (Meg arra is csak korlatozottan mivel a celtablanak akkor hasonlo szerkezetunek kell lennie)
De en csak a megvaltozott mezoket szeretnem logba kiirni.

( Yorirou | 2008. 10. 17., p – 15:33 )

Hirtelen ötlet: és ha a NEW és OLD értékét megpróbálnád átadni egy record adattípusú változónak? [most nincs előttem postgres, nem tudom, hogy megy-e]

Andi, really. Take it from me. If I tell you something, I'm usually right.

Sajnos azzal nem valtozik semmi.
A NEW es OLD valtozok mar egyebkent is rekord-ok.
Es a lokalis valtozot ugyanugy nem latom sql ben.

Ahogy nezem a plperl-lesz az en baratom ebben a kerdesben
http://www.postgresql.org/docs/8.0/interactive/plperl-triggers.html

$_TD->{new}{foo}

NEW value of column foo

$_TD->{old}{foo}

OLD value of column foo

Mivel ezek hash-ek gondolom a foo helyre akkor betehetek skalar valtozokat is.

Azert ha valakinek lenne a tarsolyban egy plpgsql-es megoldasa ne kimeljen!

( hyper80 v | 2008. 10. 18., szo – 11:23 )

A problemat vegulis megkerultem azzal hogy pl/perl ben csinaltam meg.
Es ha netan valakinek kellene ilyesmi marhasag, kozzeteszem a megfejtest:


CREATE OR REPLACE FUNCTION auto_update_logger() RETURNS trigger AS $$
 $tablename = $_TD->{relname};
 $column_num = 0;

 $query = "SELECT attname FROM pg_attribute,pg_type ".
   " WHERE typname = '$tablename' AND attrelid = typrelid AND".
   " attname NOT IN('oid','tableoid','cmin','cmax','xmin','xmax','ctid') ";
 $result = spi_exec_query($query);
 $column_num = $result->{'processed'};

 $id_val = "";
 for($i=0;$i < $column_num;$i++)
 {
  $field = $result->{rows}[$i]->{attname};
  if($field =~ /_id/)
  {
   $id_val = $_TD->{new}{$field};
  }
 }
 for($i=0;$i < $column_num;$i++)
 {
  $field = $result->{rows}[$i]->{attname};
  if($_TD->{old}{$field} ne $_TD->{new}{$field})
  {
   $query = "INSERT INTO xlog(user_id,changetime,tablename,record_id,field_id,old_value,new_value) ".
    "VALUES(current_user,now(),'$tablename','$id_val','$field','$_TD->{old}{$field}','$_TD->{new}{$field}')";
   spi_exec_query($query);
  }
 }
 return;
$$ LANGUAGE plperl;

Ennyi, a logolo tabla amit nalam ez ír:


CREATE TABLE xlog (
 user_id VARCHAR(32),
 changetime TIMESTAMP,
 tablename VARCHAR(32),
 record_id VARCHAR(32),
 field_id VARCHAR(32),
 old_value VARCHAR,
 new_value VARCHAR
);

Es igy ez a fuggveny fuggetlen a tabla szerkezetetol. (egyedul az id-t allapitja meg ugy hogy az attibutum neveben megkeresi az "_id" reszt).
Azaz barmilyen tablara raallithato.