pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: Larry Rosenman <ler@lerctr.org>
To: pgsql-sql@postgresql.org
Subject: PL/pgSQL: Logging Trigger. Advice/comments/other?
Date: Wed, 14 May 2003 15:04:21 -0500
Message-ID: <382520000.1052942661@lerlaptop-red.iadfw.net> (raw )
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_old text;
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_token NOTNULL
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_by NOTNULL
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;
IF NEW.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_asn NOTNULL
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) ;
IF OLD.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_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 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_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 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_reference NOTNULL
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_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 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) ;
IF NEW.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 AND OLD.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;
IF NEW.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_date NOTNULL 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_by NOTNULL 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_by ISNULL 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) ;
END IF;
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 AND OLD.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 AND 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 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;
IF NEW.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;
IF NEW.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_reference NOTNULL
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;
IF NEW.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;
IF NEW.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_reference ISNULL 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) ;
END IF;
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
view thread (3+ messages) latest in thread
Message-ID: <382520000.1052942661@lerlaptop-red.iadfw.net>
Permalink: ../382520000.1052942661@lerlaptop-red.iadfw.net/
Also on: postgresql.org/message-id/382520000.1052942661@lerlaptop-red.iadfw.net
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ler@lerctr.org
Subject: Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
In-Reply-To: <382520000.1052942661@lerlaptop-red.iadfw.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox