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.dinis@xxxxxxxxxxxxxxxxxx> 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 user information I'm getting, in this case "phpuser".
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
