-- start with a little preliminary infrastructure

set client_min_messages = 'warning';

drop schema accts cascade;
create schema accts;

CREATE TABLE accts.accounts (
    id serial primary key,
	name text NOT NULL
);

CREATE TABLE accts.users (
    id integer primary key,
	name text NOT NULL
);


CREATE FUNCTION accts.current_sysid() RETURNS integer 
VOLATILE LANGUAGE sql AS '
  select usesysid::integer from pg_shadow where usename = current_user
';

INSERT INTO accts.users VALUES (current_sysid(), current_user);

INSERT INTO accts.accounts (name) VALUES ('first account');

-- the main transaction table

CREATE TABLE accts.txn (
    txn_id serial PRIMARY KEY,
    account_id integer REFERENCES accts.accounts(id),
    amount numeric(10,2) NOT NULL,
    details text
);

-- the audit trace table

CREATE TABLE accts.txn_audit (
    mod text CHECK ( mod = 'INSERT' or mod = 'UPDATE' or mod = 'DELETE'),
    user_id integer REFERENCES accts.users(id),
    ts timestamp with time zone,
    new_txn accts.txn,
    old_txn accts.txn
);

-- the trigger function that hooks them together

CREATE FUNCTION accts.audit_txns() RETURNS trigger LANGUAGE plperl as $$

    my $sql = <<EOSQL;

      insert into accts.txn_audit values( 
        '$_TD->{event}',
        accts.current_sysid(),
		now(),

EOSQL

	if ($_TD->{event} eq 'DELETE')	
	{
	  $sql .= "NULL,\n";
	}
	else
	{
      $sql .= "( $_TD->{new}{txn_id}, $_TD->{new}{account_id}, " .
              "$_TD->{new}{amount} ,";
	  if (defined $_TD->{new}{details})
      {
	     (my $details = $_TD->{new}{details}) =~ s/(['\\])/\\$1/g;
         $sql .= "'$details' ), "
      }
      else
	  {
         $sql .= " NULL ), ";
      }
    }

	if ($_TD->{event} eq 'INSERT')	
	{
	  $sql .= "NULL)\n";
	}
	else
	{
      $sql .= "( $_TD->{old}{txn_id}, $_TD->{old}{account_id}, " .
              "$_TD->{old}{amount} ,";
	  if (defined $_TD->{old}{details})
      {
	     (my $details = $_TD->{old}{details}) =~ s/(['\\])/\\$1/g;
         $sql .= "'$details' )) "
      }
      else
	  {
         $sql .= " NULL )) ";
      }
    }

    my $rv = spi_exec_query($sql);

    unless ($rv->{status} eq 'SPI_OK_INSERT')
    {
      elog(NOTICE,"trigger status: $rv->{status}");
      return 'SKIP'
    }


    return undef; 
    
$$;

-- set up the trigger

CREATE TRIGGER audit_txn_trigger 
  BEFORE insert OR update OR delete ON accts.txn
  FOR EACH ROW
  EXECUTE PROCEDURE accts.audit_txns();

-- do some transactions

INSERT INTO accts.txn (account_id, amount, details) 
  VALUES (1,1000.00, 'initial ''deposit''');

UPDATE accts.txn SET details = 'modified: ' || details;

DELETE FROM accts.txn;

-- look at the audit trace

SELECT * FROM accts.txn_audit ;