Is there an opposite to pg_get_userbyid() ?

[Andreas]

Hi,
I'd like to store who changed records on some tables.
I'd prefer not to store the username but rather his/her ID.
Will I allways have to run
select usesysid from pg_user where usename=session_user;
or is there a complement to pg_get_userbyid() ?

Can I have this as a default-value for a created_by integer-collumn ?

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Michael Fuhr]

On Thu, Oct 14, 2004 at 07:42:22PM +0200, Andreas wrote:

I'd like to store who changed records on some tables.
I'd prefer not to store the username but rather his/her ID.
Will I allways have to run
select usesysid from pg_user where usename=session_user;
or is there a complement to pg_get_userbyid() ?
If there is then I've overlooked it in the documentation. It's
easy enough to write:

CREATE FUNCTION get_userbyname(NAME) RETURNS INTEGER AS '
SELECT usesysid FROM pg_user WHERE usename = $1
' LANGUAGE SQL STABLE STRICT;
Can I have this as a default-value for a created_by integer-collumn ?

You should be able to use the above function in a column's DEFAULT
expression:

CREATE TABLE changelog (
logid SERIAL PRIMARY KEY,
logtime TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
loguser INTEGER NOT NULL DEFAULT get_userbyname(CURRENT_USER),
logmsg TEXT NOT NULL
);

GRANT INSERT, SELECT ON changelog TO otheruser;
GRANT UPDATE ON changelog_logid_seq TO otheruser;

INSERT INTO changelog (logmsg) VALUES ('first message');
\c - otheruser
INSERT INTO changelog (logmsg) VALUES ('second message');
SELECT * FROM changelog;
logid | logtime | loguser | logmsg
-------+-------------------------------+---------+----------------
1 | 2004-10-14 18:43:20.581907-06 | 100 | first message
2 | 2004-10-14 18:43:35.541114-06 | 102 | second message
(2 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Andreas]

Michael Fuhr wrote:

On Thu, Oct 14, 2004 at 07:42:22PM +0200, Andreas wrote:

or is there a complement to pg_get_userbyid() ?

If there is then I've overlooked it in the documentation. It's
easy enough to write:

Thanks :)

I though it was consequent to expect such a function, since there is
pg_get_userbyid().
I'm a bit reluctant to use a real lookup-function for what I have in mind.
My application connects to the server and hopefully can keep the
connection up until the user leaves his desk. During the whole session
every insert/update will trigger those user-id lookups for an
information that is constant for the session.
I hoped there were a function that could read the user-id directly from
the connection's properties.

Well, well ... that's life ...
Andreas ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster