PL/pgSQL: Logging Trigger. Advice/comments/other? - Mailing list pgsql-sql
| From | Larry Rosenman |
|---|---|
| Subject | PL/pgSQL: Logging Trigger. Advice/comments/other? |
| Date | |
| Msg-id | 382520000.1052942661@lerlaptop-red.iadfw.net Whole thread Raw |
| Responses |
Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
(Joe Conway <mail@joeconway.com>)
|
| List | pgsql-sql |
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