Search Postgresql Archives

Session variables (how do I pass session data to a function)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have a question on how to do something with Postgres that I think
of by the name of "Session Variables".

What I want to do is build a table that will be filled in by a trigger, 
it will contains three fields in addition to the fields in the table
that causes the trigger that will contain the time, user and the "proxy
user" for the change (this is an audit table) I have the following example
that handles the first two extra fields correctly:

--------------------------------------------------------


CREATE TABLE partypersons (
        partyid TEXT,
        firstname TEXT,
        middlename TEXT,
        lastname TEXT,
        personaltitle TEXT,
        suffex TEXT,
        employmentstatus TEXT,
        residencestatus TEXT,
        maritalstatus TEXT,
        PRIMARY KEY (partyid)
        )
;

CREATE TABLE partypersons_audit (
        audit_when TIMESTAMP,
        audit_who TEXT,
        partyid TEXT,
        firstname TEXT,
        middlename TEXT,
        lastname TEXT,
        personaltitle TEXT,
        suffex TEXT,
        employmentstatus TEXT,
        residencestatus TEXT,
        maritalstatus TEXT,
        PRIMARY KEY (partyid,audit_when)
        )
;


ALTER TABLE partypersons ADD
        FOREIGN KEY ( partyid ) REFERENCES partys  ( partyid ) 
;

-- AUDIT FOR PARTYPERSON

CREATE OR REPLACE FUNCTION fauditpartypersons() RETURNS TRIGGER AS $fauditpartypersons$
DECLARE
BEGIN
        IF (TG_OP = 'UPDATE') THEN
                IF (OLD.firstname = NEW.firstname) THEN
                        NEW.firstname := NULL;
                END IF; 
                IF (OLD.middlename = NEW.middlename) THEN
                        NEW.middlename := NULL;
                END IF; 
                IF (OLD.lastname = NEW.lastname) THEN
                        NEW.lastname := NULL;
                END IF; 
                IF (OLD.personaltitle = NEW.personaltitle) THEN
                        NEW.personaltitle := NULL;
                END IF; 
                IF (OLD.suffex = NEW.suffex) THEN
                        NEW.suffex := NULL;
                END IF; 
                IF (OLD.employmentstatus = NEW.employmentstatus) THEN
                        NEW.employmentstatus := NULL;
                END IF; 
                IF (OLD.residencestatus = NEW.residencestatus) THEN
                        NEW.residencestatus := NULL;
                END IF; 
                IF (OLD.maritalstatus = NEW.maritalstatus) THEN
                        NEW.maritalstatus := NULL;
                END IF; 
        END IF;
        INSERT INTO partypersons_audit SELECT  now(), user, NEW.*;
        RETURN NULL;

END;
$fauditpartypersons$ LANGUAGE plpgsql;


CREATE TRIGGER auditpartyperson AFTER UPDATE OR INSERT
        ON partypersons FOR ROW
        EXECUTE PROCEDURE fauditpartypersons ()
        ;


--------------------------------------------------------

As you can see the primary key and the time and user are always set
while the other fields are NULL unless an update (or insert) changes
them.

What I want to do is to add to this another field that will be NULL
UNLESS the session sets a value to the "user" for that the middle-wear 
system is acting for.

What this will be used for will be a web system that will access the 
DB as the user apache but I want to do two things:

1) log to the audit table all changes even if they are done outside of
the web interface (this is done by the above system)

2) in the same log allow the system (apache) to also say who it is "acting
for"


Thanks.

-- 
This email, and any files transmitted with it, is confidential 
and intended solely for the use of the individual or entity to 
whom they are addressed.  If you have received this email in error, 
please advise postmaster@xxxxxxxxxxx <mailto:postmaster@xxxxxxxxxxx>.

New MDT Software Headquarters (As of July 1, 2008):
3480 Preston Ridge Road
Suite 450
Alpharetta, GA 30005


Philip W. Dalrymple III <pwd@xxxxxxxxxxx>
MDT Software - The Change Management Company
+1 678 297 1001
Fax +1 678 297 1003


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux