Home > mailing lists
PL/pgSQL: Logging Trigger. Advice/comments/other? - Mailing list pgsql-sql

From	Larry Rosenman
Subject	PL/pgSQL: Logging Trigger. Advice/comments/other?
Date	May 14, 2003 19:04:30
Msg-id	382520000.1052942661@lerlaptop-red.iadfw.net Whole thread Raw
Responses	Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
List	pgsql-sql
Tree view
Ok, I finally finished this damn trigger to log changes to ONE frapping 
table.

I'm wondering if any of you PL/pgSQL guru's out there see any better way to 
do
this:

CREATE OR REPLACE FUNCTION networks_trigger_log () RETURNS "trigger"   AS 'DECLARE   record_fields_old text;
record_values_oldtext;   record_fields_new text;   record_values_new text;   query_string_old text;   query_string_new
text;  BEGIN   query_string_old := ''INSERT INTO networks_log'';   query_string_new := ''INSERT INTO networks_log'';
record_fields_old:= ''user_id,update_at,update_type,update_ver'';   record_fields_new :=
''user_id,update_at,update_type,update_ver'';  record_values_old := ''CURRENT_USER,CURRENT_TIMESTAMP,'' ||
quote_literal(TG_OP)|| '','' || quote_literal(''O'');   record_values_new := ''CURRENT_USER,CURRENT_TIMESTAMP,'' ||
     quote_literal(TG_OP) || '','' || quote_literal(''N'');   IF TG_OP = ''INSERT''   THEN       record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''netblock'');       record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.netblock::inet::text) ;       IF NEW.router NOTNULL
    THEN          record_fields_new := record_fields_new || '','' ||                     quote_ident(''router'');
  record_values_new := record_values_new || '','' ||                     quote_literal(NEW.router) ;       END IF;
IF NEW.interface NOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''interface'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.interface);       END IF;       IF NEW.dest_ip NOTNULL       THEN          record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''dest_ip'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.dest_ip::text) ;       END IF;       IF
NEW.mis_tokenNOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''mis_token'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.mis_token);       END IF;       IF NEW.assigned_date NOTNULL       THEN          record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''assigned_date'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.assigned_date) ;       END IF;       IF
NEW.assigned_byNOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''assigned_by'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.assigned_by);       END IF;       IF NEW.justification_now NOTNULL       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''justification_now'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.justification_now) ;       END
IF;      IF NEW.justification_1yr NOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
            quote_ident(''justification_1yr'');          record_values_new := record_values_new || '','' ||
       quote_literal(NEW.justification_1yr) ;       END IF;       IF NEW.cust_asn NOTNULL       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''cust_asn'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.cust_asn) ;       END IF;
IFNEW.comments NOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''comments'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.comments);       END IF;       IF NEW.other_reference NOTNULL       THEN          record_fields_new
:=record_fields_new || '','' ||                     quote_ident(''other_reference'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.other_reference) ;       END IF;       IF
NEW.parent_asnNOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''parent_asn'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.parent_asn);       END IF;       IF NEW.status NOTNULL       THEN          record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''status'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.status) ;       END IF;       IF NEW.purpose NOTNULL
     THEN          record_fields_new := record_fields_new || '','' ||                     quote_ident(''purpose'');
    record_values_new := record_values_new || '','' ||                     quote_literal(NEW.purpose) ;       END IF;
   IF NEW.customer_reference NOTNULL       THEN          record_fields_new := record_fields_new || '','' ||
       quote_ident(''customer_reference'');          record_values_new := record_values_new || '','' ||
   quote_literal(NEW.customer_reference) ;       END IF;       IF NEW.natblock NOTNULL       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''natblock'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.natblock) ;       END IF;
query_string_new:= query_string_new || ''('' ||                    record_fields_new || '') VALUES('' ||
   record_values_new || '')'';       EXECUTE query_string_new;       RETURN new;   END IF;   IF TG_OP = ''DELETE''
THEN      record_fields_old := record_fields_old || '','' ||                     quote_ident(''netblock'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.netblock::inet::text) ;
IFOLD.router NOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''router'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.router);       END IF;       IF OLD.interface NOTNULL       THEN          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''interface'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.interface) ;       END IF;       IF OLD.dest_ip
NOTNULL      THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''dest_ip'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.dest_ip::text);       END IF;       IF OLD.mis_token NOTNULL       THEN          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''mis_token'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.mis_token) ;       END IF;       IF
OLD.assigned_dateNOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''assigned_date'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.assigned_date);       END IF;       IF OLD.assigned_by NOTNULL       THEN          record_fields_old
:=record_fields_old || '','' ||                     quote_ident(''assigned_by'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.assigned_by) ;       END IF;       IF
OLD.justification_nowNOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''justification_now'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.justification_now);       END IF;       IF OLD.justification_1yr NOTNULL       THEN
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''justification_1yr'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.justification_1yr) ;       END
IF;      IF OLD.cust_asn NOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
   quote_ident(''cust_asn'');          record_values_old := record_values_old || '','' ||
quote_literal(OLD.cust_asn);       END IF;       IF OLD.comments NOTNULL       THEN          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''comments'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.comments) ;       END IF;       IF
OLD.other_referenceNOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''other_reference'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.other_reference);       END IF;       IF OLD.parent_asn NOTNULL       THEN          record_fields_old
:=record_fields_old || '','' ||                     quote_ident(''parent_asn'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.parent_asn) ;       END IF;       IF OLD.status
NOTNULL      THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''status'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.status);       END IF;       IF OLD.purpose NOTNULL       THEN          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''purpose'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.purpose) ;       END IF;       IF
OLD.customer_referenceNOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''customer_reference'');          record_values_old := record_values_old || '','' ||
quote_literal(OLD.customer_reference);       END IF;       IF OLD.natblock NOTNULL       THEN
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''natblock'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.natblock) ;       END IF;
query_string_old:= query_string_old || ''('' ||                    record_fields_old || '') VALUES('' ||
   record_values_old || '')'';       EXECUTE query_string_old;       RETURN old;   END IF;   IF TG_OP = ''UPDATE''
THEN      record_fields_old := record_fields_old || '','' ||                     quote_ident(''netblock'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.netblock::inet::text) ;
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''netblock'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.netblock::inet::text) ;
IFNEW.router NOTNULL  AND OLD.router NOTNULL AND         NEW.router <> OLD.router       THEN          record_fields_new
:=record_fields_new || '','' ||                     quote_ident(''router'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.router) ;          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''router'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.router) ;       END IF;       IF NEW.router ISNULL
ANDOLD.router NOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''router'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.router);       END IF;       IF NEW.router NOTNULL  AND OLD.router ISNULL       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''router'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.router) ;       END IF;
IFNEW.interface NOTNULL  AND OLD.interface NOTNULL AND         NEW.interface <> OLD.interface       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''interface'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.interface) ;
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''interface'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.interface) ;       END IF;
IF NEW.interface ISNULL  AND OLD.interface NOTNULL       THEN          record_fields_old := record_fields_old || '',''
||                    quote_ident(''interface'');          record_values_old := record_values_old || '','' ||
         quote_literal(OLD.interface) ;       END IF;       IF NEW.interface NOTNULL  AND OLD.interface ISNULL
THEN         record_fields_new := record_fields_new || '','' ||                     quote_ident(''interface'');
record_values_new := record_values_new || '','' ||                     quote_literal(NEW.interface) ;       END IF;
 IF NEW.dest_ip NOTNULL  AND OLD.dest_ip NOTNULL AND         NEW.dest_ip <> OLD.dest_ip       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''dest_ip'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.dest_ip::text) ;
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''dest_ip'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.dest_ip::text) ;       END IF;
    IF NEW.dest_ip ISNULL  AND OLD.dest_ip NOTNULL       THEN          record_fields_old := record_fields_old || '',''
||                    quote_ident(''dest_ip'');          record_values_old := record_values_old || '','' ||
       quote_literal(OLD.dest_ip::text) ;       END IF;       IF NEW.dest_ip NOTNULL  AND OLD.dest_ip ISNULL       THEN
        record_fields_new := record_fields_new || '','' ||                     quote_ident(''dest_ip'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.dest_ip::text) ;       END IF;
    IF NEW.mis_token NOTNULL  AND OLD.mis_token NOTNULL AND         NEW.mis_token <> OLD.mis_token       THEN
record_fields_new:= record_fields_new || '','' ||                     quote_ident(''mis_token'');
record_values_new:= record_values_new || '','' ||                     quote_literal(NEW.mis_token) ;
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''mis_token'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.mis_token) ;       END IF;
IF NEW.mis_token ISNULL  AND OLD.mis_token NOTNULL       THEN          record_fields_old := record_fields_old || '',''
||                    quote_ident(''mis_token'');          record_values_old := record_values_old || '','' ||
         quote_literal(OLD.mis_token) ;       END IF;       IF NEW.mis_token NOTNULL  AND OLD.mis_token ISNULL
THEN         record_fields_new := record_fields_new || '','' ||                     quote_ident(''mis_token'');
record_values_new := record_values_new || '','' ||                     quote_literal(NEW.mis_token) ;       END IF;
 IF NEW.assigned_date NOTNULL  AND OLD.assigned_date NOTNULL AND         NEW.assigned_date <> OLD.assigned_date
THEN         record_fields_new := record_fields_new || '','' ||                     quote_ident(''assigned_date'');
    record_values_new := record_values_new || '','' ||                     quote_literal(NEW.assigned_date) ;
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''assigned_date'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.assigned_date) ;       END IF;
    IF NEW.assigned_date ISNULL  AND OLD.assigned_date NOTNULL       THEN          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''assigned_date'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.assigned_date) ;       END IF;       IF
NEW.assigned_dateNOTNULL  AND OLD.assigned_date ISNULL       THEN          record_fields_new := record_fields_new ||
'',''||                     quote_ident(''assigned_date'');          record_values_new := record_values_new || '','' ||
                   quote_literal(NEW.assigned_date) ;       END IF;       IF NEW.assigned_by NOTNULL  AND
OLD.assigned_byNOTNULL AND         NEW.assigned_by <> OLD.assigned_by       THEN          record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''assigned_by'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.assigned_by) ;          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''assigned_by'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.assigned_by) ;       END IF;       IF
NEW.assigned_byISNULL  AND OLD.assigned_by NOTNULL       THEN          record_fields_old := record_fields_old || '',''
||                    quote_ident(''assigned_by'');          record_values_old := record_values_old || '','' ||
           quote_literal(OLD.assigned_by) ;       END IF;       IF NEW.assigned_by NOTNULL  AND OLD.assigned_by ISNULL
    THEN          record_fields_new := record_fields_new || '','' ||                     quote_ident(''assigned_by'');
       record_values_new := record_values_new || '','' ||                     quote_literal(NEW.assigned_by) ;
ENDIF;       IF NEW.justification_now NOTNULL  AND OLD.justification_now NOTNULL 
 
AND         NEW.justification_now <> OLD.justification_now       THEN          record_fields_new := record_fields_new
||'','' ||                     quote_ident(''justification_now'');          record_values_new := record_values_new ||
'',''||                     quote_literal(NEW.justification_now) ;          record_fields_old := record_fields_old ||
'',''||                     quote_ident(''justification_now'');          record_values_old := record_values_old ||
'',''||                     quote_literal(OLD.justification_now) ;       END IF;       IF NEW.justification_now ISNULL
ANDOLD.justification_now NOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
   quote_ident(''justification_now'');          record_values_old := record_values_old || '','' ||
quote_literal(OLD.justification_now);       END IF;       IF NEW.justification_now NOTNULL  AND OLD.justification_now
ISNULL      THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''justification_now'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.justification_now);       END IF;       IF NEW.justification_1yr NOTNULL  AND OLD.justification_1yr
NOTNULL
 
AND         NEW.justification_1yr <> OLD.justification_1yr       THEN          record_fields_new := record_fields_new
||'','' ||                     quote_ident(''justification_1yr'');          record_values_new := record_values_new ||
'',''||                     quote_literal(NEW.justification_1yr) ;          record_fields_old := record_fields_old ||
'',''||                     quote_ident(''justification_1yr'');          record_values_old := record_values_old ||
'',''||                     quote_literal(OLD.justification_1yr) ;       END IF;       IF NEW.justification_1yr ISNULL
ANDOLD.justification_1yr NOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
   quote_ident(''justification_1yr'');          record_values_old := record_values_old || '','' ||
quote_literal(OLD.justification_1yr);       END IF;       IF NEW.justification_1yr NOTNULL  AND OLD.justification_1yr
ISNULL      THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''justification_1yr'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.justification_1yr);       END IF;       IF NEW.cust_asn NOTNULL  AND OLD.cust_asn NOTNULL AND
NEW.cust_asn<> OLD.cust_asn       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''cust_asn'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.cust_asn);          record_fields_old := record_fields_old || '','' ||
quote_ident(''cust_asn'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.cust_asn);       END IF;       IF NEW.cust_asn ISNULL  AND OLD.cust_asn NOTNULL       THEN
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''cust_asn'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.cust_asn) ;       END IF;
IFNEW.cust_asn NOTNULL  AND OLD.cust_asn ISNULL       THEN          record_fields_new := record_fields_new || '','' ||
                  quote_ident(''cust_asn'');          record_values_new := record_values_new || '','' ||
    quote_literal(NEW.cust_asn) ;       END IF;       IF NEW.comments NOTNULL  AND OLD.comments NOTNULL AND
NEW.comments<> OLD.comments       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''comments'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.comments);          record_fields_old := record_fields_old || '','' ||
quote_ident(''comments'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.comments);       END IF;       IF NEW.comments ISNULL  AND OLD.comments NOTNULL       THEN
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''comments'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.comments) ;       END IF;
IFNEW.comments NOTNULL  AND OLD.comments ISNULL       THEN          record_fields_new := record_fields_new || '','' ||
                  quote_ident(''comments'');          record_values_new := record_values_new || '','' ||
    quote_literal(NEW.comments) ;       END IF;       IF NEW.other_reference NOTNULL  AND OLD.other_reference NOTNULL
AND        NEW.other_reference <> OLD.other_reference       THEN          record_fields_new := record_fields_new ||
'',''||                     quote_ident(''other_reference'');          record_values_new := record_values_new || '',''
||                    quote_literal(NEW.other_reference) ;          record_fields_old := record_fields_old || '','' ||
                  quote_ident(''other_reference'');          record_values_old := record_values_old || '','' ||
           quote_literal(OLD.other_reference) ;       END IF;       IF NEW.other_reference ISNULL  AND
OLD.other_referenceNOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''other_reference'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.other_reference);       END IF;       IF NEW.other_reference NOTNULL  AND OLD.other_reference ISNULL
    THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''other_reference'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.other_reference);       END IF;       IF NEW.parent_asn NOTNULL  AND OLD.parent_asn NOTNULL AND
 NEW.parent_asn <> OLD.parent_asn       THEN          record_fields_new := record_fields_new || '','' ||
    quote_ident(''parent_asn'');          record_values_new := record_values_new || '','' ||
quote_literal(NEW.parent_asn);          record_fields_old := record_fields_old || '','' ||
quote_ident(''parent_asn'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.parent_asn);       END IF;       IF NEW.parent_asn ISNULL  AND OLD.parent_asn NOTNULL       THEN
   record_fields_old := record_fields_old || '','' ||                     quote_ident(''parent_asn'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.parent_asn) ;       END IF;
 IF NEW.parent_asn NOTNULL  AND OLD.parent_asn ISNULL       THEN          record_fields_new := record_fields_new ||
'',''||                     quote_ident(''parent_asn'');          record_values_new := record_values_new || '','' ||
                quote_literal(NEW.parent_asn) ;       END IF;       IF NEW.status NOTNULL  AND OLD.status NOTNULL AND
     NEW.status <> OLD.status       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''status'');          record_values_new := record_values_new || '','' ||
quote_literal(NEW.status);          record_fields_old := record_fields_old || '','' ||
quote_ident(''status'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.status);       END IF;       IF NEW.status ISNULL  AND OLD.status NOTNULL       THEN
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''status'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.status) ;       END IF;
IFNEW.status NOTNULL  AND OLD.status ISNULL       THEN          record_fields_new := record_fields_new || '','' ||
              quote_ident(''status'');          record_values_new := record_values_new || '','' ||
quote_literal(NEW.status);       END IF;       IF NEW.purpose NOTNULL  AND OLD.purpose NOTNULL AND         NEW.purpose
<>OLD.purpose       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''purpose'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.purpose);          record_fields_old := record_fields_old || '','' ||
quote_ident(''purpose'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.purpose);       END IF;       IF NEW.purpose ISNULL  AND OLD.purpose NOTNULL       THEN
record_fields_old:= record_fields_old || '','' ||                     quote_ident(''purpose'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.purpose) ;       END IF;
IFNEW.purpose NOTNULL  AND OLD.purpose ISNULL       THEN          record_fields_new := record_fields_new || '','' ||
                quote_ident(''purpose'');          record_values_new := record_values_new || '','' ||
 quote_literal(NEW.purpose) ;       END IF;       IF NEW.customer_reference NOTNULL  AND OLD.customer_reference 
 
NOTNULL AND         NEW.customer_reference <> OLD.customer_reference       THEN          record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''customer_reference'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.customer_reference) ;          record_fields_old :=
record_fields_old|| '','' ||                     quote_ident(''customer_reference'');          record_values_old :=
record_values_old|| '','' ||                     quote_literal(OLD.customer_reference) ;       END IF;       IF
NEW.customer_referenceISNULL  AND OLD.customer_reference 
 
NOTNULL       THEN          record_fields_old := record_fields_old || '','' ||
quote_ident(''customer_reference'');         record_values_old := record_values_old || '','' ||
quote_literal(OLD.customer_reference);       END IF;       IF NEW.customer_reference NOTNULL  AND
OLD.customer_reference
 
ISNULL       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''customer_reference'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.customer_reference);       END IF;       IF NEW.natblock NOTNULL  AND OLD.natblock NOTNULL AND
NEW.natblock <> OLD.natblock       THEN          record_fields_new := record_fields_new || '','' ||
quote_ident(''natblock'');         record_values_new := record_values_new || '','' ||
quote_literal(NEW.natblock::inet::text);          record_fields_old := record_fields_old || '','' ||
quote_ident(''natblock'');          record_values_old := record_values_old || '','' ||
quote_literal(OLD.natblock::inet::text);       END IF;       IF NEW.natblock ISNULL  AND OLD.natblock NOTNULL
THEN         record_fields_old := record_fields_old || '','' ||                     quote_ident(''natblock'');
record_values_old:= record_values_old || '','' ||                     quote_literal(OLD.natblock::inet::text) ;
ENDIF;       IF NEW.natblock NOTNULL  AND OLD.natblock ISNULL       THEN          record_fields_new :=
record_fields_new|| '','' ||                     quote_ident(''natblock'');          record_values_new :=
record_values_new|| '','' ||                     quote_literal(NEW.natblock::inet::text) ;       END IF;
query_string_old:= query_string_old || ''('' ||                    record_fields_old || '') VALUES('' ||
   record_values_old || '')'';       EXECUTE query_string_old;       query_string_new := query_string_new || ''('' ||
                record_fields_new || '') VALUES('' ||                    record_values_new || '')'';       EXECUTE
query_string_new;      RETURN new;   END IF;   END;'   LANGUAGE plpgsql;
 


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
pgsql-sql by date:
From: Tom Lane
Date: 14 May 2003, 17:16:56
Subject: Re: Followup from yesterday's PL/pgSQL fun...
From: Ian Barwick
Date: 14 May 2003, 20:19:07
Subject: Re: Using psql to insert character codes
PL/pgSQL: Logging Trigger. Advice/comments/other? - Mailing list pgsql-sql

Previous

Next
