To understand Recursion - Part II

Hi all (again),

I assembled a (again) very nice and practical introduction to Recursion !!
Here is the link

Understanding Recursion - Part I

Hi all,

I assembled a very nice and practical introduction to Recursion !!
Here is the link

ONE Trigger Function to rule them all

In many of my earlier applications/frameworks av always implemented table audit facility via java/python code...

granted this worked ok... unless the (malicious or otherwise) user accessed the apps DB via other means eg terminal, ide, et al...  

Recently av been doing this via DB triggers (...silence please.. i know this is not DB agnostic...) and had quite a challenge creating a 'universal' trigger function that will be used by all auditable tables...

The following is the solution, of course assembled from several blogs and sites and some fiddling on SQLFiddle......

Prerequisites (for those who wish to use this function as it is):

• Audited table must capture a login id(bigint)
• Must have an AFTER trigger calling this fxn
• Table p_audit must be created in advance

CREATE OR REPLACE FUNCTION if_modified_func() returns TRIGGER AS $$
DECLARE
      pk        bigint;
      pk_name        varchar(100);
      pk_datatype    varchar(50);
BEGIN

    IF TG_WHEN <> 'AFTER' THEN
         RAISE EXCEPTION 'Audit Function may only run as an AFTER trigger';
    END IF;

    --first we identify the pk field of the db
    EXECUTE
    '(SELECT
        pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
      FROM pg_index, pg_class, pg_attribute
      WHERE pg_class.oid = ''' || TG_TABLE_NAME || '''::regclass
        AND indrelid = pg_class.oid
        AND pg_attribute.attrelid = pg_class.oid
        AND pg_attribute.attnum = any(pg_index.indkey)
        AND indisprimary)'
      INTO pk_name, pk_datatype;

    --TO GET record_id start here => http://www.sqlfiddle.com/#!15/64e41/2/0

    if TG_OP = 'UPDATE' then
        EXECUTE 'SELECT $1.'||pk_name INTO pk USING NEW;

        INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
                  record_id, db_user, action_timestamp, data_before, data_after, updated_cols, narrative)
        VALUES(txid_current(), NEW.login_id, TG_TABLE_NAME, 'U', inet_client_addr(), inet_client_port(), current_query(),
                pk, CURRENT_USER, CURRENT_TIMESTAMP, hstore(OLD.*), hstore(NEW.*), akeys(hstore(NEW.*) - hstore(OLD.*)), NULL);

        return NEW;
    elsif TG_OP = 'DELETE' then
        EXECUTE 'SELECT $1.'||pk_name INTO pk USING OLD;

        INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
                  record_id, db_user, action_timestamp, data_before)
        VALUES(txid_current(), OLD.login_id, TG_TABLE_NAME, 'D', inet_client_addr(), inet_client_port(), current_query(),
                pk, CURRENT_USER, CURRENT_TIMESTAMP, hstore(OLD.*));

        return OLD;
    elsif TG_OP = 'INSERT' then
        EXECUTE 'SELECT $1.'||pk_name INTO pk USING NEW;

        INSERT INTO p_audit (transaction_id, p_login_id, table_name, action, client_addr, client_port, client_query,
              record_id, db_user, action_timestamp, data_before, data_after, updated_cols, narrative)
        VALUES(txid_current(), NEW.login_id, TG_TABLE_NAME, 'I', inet_client_addr(), inet_client_port(), current_query(),
                pk,  CURRENT_USER, CURRENT_TIMESTAMP, NULL, hstore(NEW.*), NULL, NULL);

        return NEW;
    end if;
END;
$$ language plpgsql;


Finaly...
if you think this is coool....... just say it

=====
Graphically  u may present it like this