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/