Gabriel, what you are looking for is also called "session variables". There are essentially 2 kind of receipes in the wild:
a) store those session information in temporary tables b) store those session information in shared memory version a) has the advantage that it can be done via plpgsql, and the disadvantage of polluting the temporary-table-space version b) has the advantage of keeping the temporary table space tidy; and the disadvantage that it needs a language with access to shared memory; which is most effectivly done via C. Somewhere there is a code example for it; google for postgresql session variables. I am using version a) for some time; in the following my functions set_user(integer) and get_user() returns integer, which set and get a user_id. Translating them to set / get a text value is left as an exercise :) Additional to get_user() -> it returns 0 (not NULL) when no user is set_userd() 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; Best wishes, Harald On Wed, Nov 3, 2010 at 13:04, Gabriel Dinis < gabriel.di...@vigiesolutions.com> wrote: > Dear all, > > Imagine I have two users "Maria" and "Ana" using a PHP site. > There is a common Postgres user "phpuser" for both. > I'm creating audit tables to track the actions made by each PHP site user. > > *I have used the following code:* > > CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$ > > BEGIN > -- > -- Create a row in MinUser_Audit to reflect the operation performed > on MinUser, > > -- make use of the special variable TG_OP to work out the operation. > -- > IF (TG_OP = 'DELETE') THEN > > INSERT INTO MinUser_audit VALUES (DEFAULT, 'D', now(), *user*, > OLD.*); > > RETURN OLD; > ELSIF (TG_OP = 'UPDATE') THEN > INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), *user*, > NEW.*); > > RETURN NEW; > ELSIF (TG_OP = 'INSERT') THEN > INSERT INTO MinUser_audit VALUES (DEFAULT, 'I', now(), *user*, > NEW.*); > > RETURN NEW; > END IF; > RETURN NULL; -- result is ignored since this is an AFTER trigger > > END; > $usr_audit$ LANGUAGE plpgsql; > > > Everything seems to wok fine except the *use*r information I'm getting, in > this case "*phpuse*r". > I would like to have not the postgres user but the PHP site user (*Maria > or Ana*). > > How can I pass the PHP site user (Maria or Ana) into Postgres in a clever > way? > > > I have done several web searches and found nothing for Postgres. I found a > solution for oracle: > http://www.oracle.com/technetwork/articles/dsl/php-web-auditing-171451.html > * > They use a "client identifier" feature.* Is there a similar way to do this > in Postgres? > > Thanks in advance. > Gabriel > > > > > > -- GHUM GmbH Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 Amtsgericht Stuttgart, HRB 734971 - persuadere. et programmare