DRY postgresql 9.4 trigger functions

Question

I've written 3 functions to log transactions to designated tables:

CREATE OR REPLACE FUNCTION log_sites() RETURNS TRIGGER AS $body$
DECLARE
    target_row sites%ROWTYPE;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        -- No NEW row
        target_row = OLD;
    ELSE
        target_row = NEW;
    END IF;
    INSERT INTO sites_history (transaction_type,
                               transaction_time,
                               site_id,
                               address,
                               name,
                               shared_key)
    VALUES (TG_OP,
            NOW(),
            target_row.site_id,
            target_row.address,
            target_row.name,
            target_row.shared_key);
RETURN target_row;
END;
$body$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION log_licenses() RETURNS TRIGGER AS $body$
DECLARE
    target_row licenses%ROWTYPE;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        target_row = OLD;
    ELSE
        target_row = NEW;
    END IF;
    INSERT INTO licenses_history (transaction_type,
                                  transaction_time,
                                  license_id,
                                  start_date,
                                  expiration_date,
                                  site_id)
    VALUES (TG_OP,
            NOW(),
            target_row.license_id,
            target_row.start_date,
            target_row.expiration_date,
            target_row.site_id);
    RETURN target_row;
    END;
    $body$
    LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION log_clients() RETURNS TRIGGER AS $body$
DECLARE
    target_row clients%ROWTYPE;
BEGIN
    IF (TG_OP = 'DELETE') THEN
        target_row = OLD;
    ELSE
        target_row = NEW;
    END IF;
    INSERT INTO clients_history (transaction_type,
                                 transaction_time,
                                 mac_address,
                                 hardware,
                                 license_id,
                                 site_id)
    VALUES (TG_OP,
            NOW(),
            target_row.mac_address,
            target_row.hardware,
            target_row.license_id,
            target_row.site_id);
    RETURN target_row;
    END;
    $body$
    LANGUAGE plpgsql;

This results in a big ugly block of PL/pgSQL, which nobody at my job is especially familiar with, myself included. A coworker suggested it'd be nice to consolidate/DRYify all this stuff, but for the life of me, I'm not sure how, especially considering each table needs a separate trigger, and the way triggers pass data to their functions. Any suggestions?

ETA: 1) Here are the triggers:

CREATE TRIGGER sites_log
AFTER INSERT OR UPDATE OR DELETE
ON sites
FOR EACH ROW EXECUTE PROCEDURE log_transactions();

CREATE TRIGGER licenses_log
    AFTER INSERT OR UPDATE OR DELETE
    ON licenses
    FOR EACH ROW EXECUTE PROCEDURE log_transactions();

CREATE TRIGGER clients_log
    AFTER INSERT OR UPDATE OR DELETE
    ON clients
    FOR EACH ROW EXECUTE PROCEDURE log_transactions();

Here's what I've got now, after quite a bit of messing around:

CREATE OR REPLACE FUNCTION log_transactions() RETURNS TRIGGER LANGUAGE plpgsql AS $body$
DECLARE
    target_row RECORD;
    target_cols text[];
    col_name RECORD;
    col_name_str text;
    right_now timestamp without time zone;
    q_str text;
BEGIN
    right_now := now();
    target_cols := '{}';
    FOR col_name IN SELECT column_name::text FROM information_schema.columns WHERE table_name = TG_TABLE_NAME AND table_schema = TG_TABLE_SCHEMA LOOP
        col_name_str := col_name.column_name::text;
        target_cols = ARRAY_APPEND(target_cols, col_name_str);
    END LOOP;
    RAISE NOTICE 'target_cols: %', target_cols;
    IF (TG_OP = 'DELETE') THEN
        target_row := OLD;
    ELSE
        target_row := NEW;
    END IF; 

    RAISE NOTICE 'target_row: %', target_row;
    EXECUTE format('INSERT INTO %I_history (transaction_time, transaction_type) VALUES (%L, %L)', TG_TABLE_NAME, right_now, TG_OP);
    q_str := format('UPDATE %I_history SET (%s) = ', TG_TABLE_NAME, array_to_string(target_cols, ', ')) || '$1' || format(' WHERE transaction_type = %L AND transaction_time = %L', TG_OP, right_now);
    EXECUTE q_str USING target_row;
    RETURN target_row;
END;
$body$;

This doesn't work either, and it's spiraling out of control, complexity-wise.

http://stackoverflow.com/q/15567503/330315 and http://stackoverflow.com/q/23247105/330315 and http://stackoverflow.com/q/1997337/330315 and http://stackoverflow.com/q/7519044/330315 — , May 07 '15 at 20:35
Use `EXECUTE` and dynamic SQL. There are plenty of examples on the wiki. (The irony of posting a FAQ about DRY is not lost to me). — Craig Ringer, May 08 '15 at 09:47
You should add the actual trigger(s) to go with the functions, or there is room for ambiguities / errors. And some description of what the triggers are supposed to do *exactly*. — Erwin Brandstetter, May 08 '15 at 17:13

score 0 · Answer 1 · answered May 09 '15 at 07:42

Personally, I use a set of home-grown functions Below for auditing any table I want. All I do is run audit.enable() on any table I want to audit and it's stored in the general tables I have here. Yes, it's not exactly what you're doing, but it's the most "DRY" think there is really, I do it once and never again-- ever.

CREATE TABLE audit.audit_log 
(
    audit_log_seq serial primary key,
    schema_name text NOT NULL,
    table_name text NOT NULL,
    db_username text,
    user_seq bigint,
    logged_ip inet,
    log_timestamp TIMESTAMP NOT NULL DEFAULT (now()),
    action TEXT NOT NULL CHECK (action IN ('I','D','U')),
    comment varchar(500),
    old_data hstore,
    new_data hstore,
    query text
) WITH 
(
    fillfactor=100
);

CREATE INDEX audit_log_schema_table_idx ON audit.audit_log(schema_name,table_name);
CREATE INDEX audit_log_timestamp_utc_idx ON audit.audit_log(log_timestamp);
CREATE INDEX audit_log_uname on audit.audit_log(user_seq);



-- generic function for all tables


CREATE OR REPLACE FUNCTION audit.log_func() RETURNS TRIGGER AS $body$
DECLARE
    v_old_data hstore;
    v_new_data hstore;  
    v_query text;
    v_comment varchar;
--  v_old_data TEXT;
--  v_new_data TEXT;
BEGIN
    v_query=current_query();

    IF (TG_OP = 'UPDATE') THEN
        v_old_data := hstore(OLD.*);
        v_new_data := hstore(NEW.*);
        v_comment=v_new_data -> 'audit_comment';
    ELSIF (TG_OP = 'DELETE') THEN
        v_old_data := hstore(OLD.*);
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := hstore(NEW.*);
    v_comment=v_new_data -> 'audit_comment';
    ELSE
        RAISE WARNING '[audit.log_func] - Other action occurred: %, at %',TG_OP,now();
        RETURN NULL;
    END IF;


    INSERT INTO audit.audit_log (schema_name,table_name,db_username,user_seq,logged_ip,action,old_data,new_data,query, comment) 
            VALUES (TG_TABLE_SCHEMA::TEXT,
            TG_TABLE_NAME::TEXT,
            session_user::TEXT,
            coalesce(current_setting('mvc.user_seq'),'0')::bigint, --current user
            inet_client_addr(),
            substring(TG_OP,1,1),
            v_old_data,
            v_new_data,
            v_query,
           v_comment);

    IF (TG_OP = 'DELETE') THEN
        RETURN OLD;
    END IF;
    RETURN NEW;

EXCEPTION
    WHEN data_exception THEN
        RAISE WARNING '[audit.log_func] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    WHEN unique_violation THEN
        RAISE WARNING '[audit.log_func] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
        RETURN NULL;
    --WHEN OTHERS THEN
    --    RAISE WARNING '[audit.log_func] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
    --    RETURN NULL;
END;
$body$ language plpgsql security definer;

CREATE OR REPLACE FUNCTION audit.enable(p_table_name text,p_schema_name text DEFAULT 'dallas') RETURNS VOID as $body$
DECLARE
BEGIN
EXECUTE 'create trigger trg_audit_'||p_table_name||' BEFORE INSERT OR UPDATE OR DELETE ON '||p_schema_name||'.'||p_table_name|| ' FOR EACH ROW EXECUTE PROCEDURE audit.log_func()'; 
exception when duplicate_object then null;
END;
$body$ language plpgsql security definer;

DRY postgresql 9.4 trigger functions

1 Answers1