pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/pgSQL: Logging Trigger. Advice/comments/other?
3+ messages / 2 participants
[nested] [flat]
* PL/pgSQL: Logging Trigger. Advice/comments/other?
@ 2003-05-14 20:04 Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Larry Rosenman @ 2003-05-14 20:04 UTC (permalink / raw)
To: 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_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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
@ 2003-05-14 21:32 Joe Conway <mail@joeconway.com>
parent: Larry Rosenman <ler@lerctr.org>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Conway @ 2003-05-14 21:32 UTC (permalink / raw)
To: Larry Rosenman <ler@lerctr.org>; +Cc: pgsql-sql
Larry Rosenman wrote:
> 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:
I haven't had the time to follow this thread, but depending on your
needs, maybe this will help (uses dblink_current_query() from
contrib/dblink):
create table networks_log(
id serial,
ts timestamp default now(),
sql text
);
create or replace function test_trig() returns trigger as '
declare
sqltext text;
begin
select into sqltext dblink_current_query();
insert into networks_log(sql) values (sqltext);
if TG_OP = ''INSERT'' or TG_OP = ''UPDATE'' then
return new;
else
return old;
end if;
end;
' language 'plpgsql';
create table networks(id serial, interface text);
create trigger networks_trig
after update or insert or delete on networks
for each row execute procedure test_trig();
insert into networks(interface) values ('eth0');
insert into networks(interface) values ('eth1');
update networks set interface = 'eth3' where id = 2;
delete from networks where id = 1;
regression=# select * from networks;
id | interface
----+-----------
2 | eth3
(1 row)
regression=# select ts::time, sql from networks_log;
ts | sql
-----------------+------------------------------------------------------
13:49:29.395334 | insert into networks(interface) values ('eth0');
13:49:34.818366 | insert into networks(interface) values ('eth1');
13:49:39.607128 | update networks set interface = 'eth3' where id = 2;
13:49:42.797973 | delete from networks where id = 1;
(4 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PL/pgSQL: Logging Trigger. Advice/comments/other?
@ 2003-05-14 21:41 Larry Rosenman <ler@lerctr.org>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Larry Rosenman @ 2003-05-14 21:41 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
--On Wednesday, May 14, 2003 14:32:52 -0700 Joe Conway <mail@joeconway.com>
wrote:
> Larry Rosenman wrote:
>> 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:
>
> I haven't had the time to follow this thread, but depending on your
> needs, maybe this will help (uses dblink_current_query() from
> contrib/dblink):
[snip]
Oooohhhhh.... This looks promising. (now, can I migrate the damn 7.2.1
to 7.3.2????).
Thanks Joe!
LER
--
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-05-14 21:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-14 20:04 PL/pgSQL: Logging Trigger. Advice/comments/other? Larry Rosenman <ler@lerctr.org>
2003-05-14 21:32 ` Joe Conway <mail@joeconway.com>
2003-05-14 21:41 ` Larry Rosenman <ler@lerctr.org>
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