Re: Is there an opposite to pg_get_userbyid() ?

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/