Обсуждение: Session variables (how do I pass session data to a function)
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@mdtsoft.com <mailto:postmaster@mdtsoft.com>. New MDT Software Headquarters (As of July 1, 2008): 3480 Preston Ridge Road Suite 450 Alpharetta, GA 30005 Philip W. Dalrymple III <pwd@mdtsoft.com> MDT Software - The Change Management Company +1 678 297 1001 Fax +1 678 297 1003
Philip, "session variables" is the perfect name for this kind of use; and googling it up some times ago lead me to a) a temp table implementation b) some shared memory implementation of these I can present you with a), written to store an integer user-ID; you can adjust it accordingly: CREATE OR REPLACE FUNCTION set_user(myid_user integer) RETURNS integer AS $BODY$ BEGIN perform relname from pg_class where relname = 'icke_tmp' and case when has_schema_privilege(relnamespace, 'USAGE') then pg_table_is_visible(oid) else false end; if not found then create temporary table icke_tmp ( id_user integer ); else delete from icke_tmp; end if; insert into icke_tmp values (myid_user); RETURN 0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; CREATE OR REPLACE FUNCTION get_user() RETURNS integer AS $BODY$ declare ergebnis int4; BEGIN perform relname from pg_class where relname = 'icke_tmp' and case when has_schema_privilege(relnamespace, 'USAGE') then pg_table_is_visible(oid) else false end; if not found then return 0; else select id_user from icke_tmp into ergebnis; end if; if not found then ergebnis:=0; end if; RETURN ergebnis; END; $BODY$ LANGUAGE 'plpgsql' STABLE COST 100; You can adjust that "0" to NULL or whatever should be the default for your application. In mine I default to 0; with 0 being something like "testuser". best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!
Philip W. Dalrymple wrote: > > 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. This can be done via a setting. See http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE If you'd like to read a fuller discussion of this very topic, search for a thread titled "can I define own variables" in the archives. I had to do the same thing back then. -- Guy Rouillier
Just to complete the answer in this thread; the trick is to do the following: - define in your postgresql.conf: custom_variable_classes = 'myvar' Then see http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE ( with the variables named myvar.*) thanks for everyone's help on this. ----- Original Message ----- From: "Guy Rouillier" <guyr-ml1@burntmail.com> To: "General Postgres Mailing List" <pgsql-general@postgresql.org> Sent: Monday, October 20, 2008 7:22:22 PM GMT -05:00 US/Canada Eastern Subject: Re: [GENERAL] Session variables (how do I pass session data to a function) Philip W. Dalrymple wrote: > > 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. This can be done via a setting. See http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-SET-TABLE If you'd like to read a fuller discussion of this very topic, search for a thread titled "can I define own variables" in the archives. I had to do the same thing back then. -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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@mdtsoft.com <mailto:postmaster@mdtsoft.com>. New MDT Software Headquarters (As of July 1, 2008): 3480 Preston Ridge Road Suite 450 Alpharetta, GA 30005 Philip W. Dalrymple III <pwd@mdtsoft.com> MDT Software - The Change Management Company +1 678 297 1001 Fax +1 678 297 1003