New buildin function

On 23 Jan 2003, Rod Taylor wrote:

RT>On Wed, 2003-01-22 at 08:09, Olleg Samoylov wrote:
RT>> Hi!
RT>>
RT>> What about adding new function:
RT>> pg_uid()
RT>> pg_session_uid()
RT>>
RT>> as reference to internal function GetUserId() and GetSessionUserId().
RT>>
RT>> These can help useful, for instance in row based securety.
RT>
RT>Do CURRENT_USER and SESSION_USER not give those values?

Nope. CURRENT_USER and SESSION_USER return username. Sometimes need uid,
it's key usesysid in table pg_shadow, for instance, for row based
permissions. Explain in example:

create table role {
role smallinteger, -- analog group of users
name text
}

create table permission { -- link role with pg_user
uid integer references pg_user(usesysid),
role smallint references role
}

create table protected_table {
-- payload fields
access smallint references role,
author_of_last_changes integer references pg_user(usesysid) default
PG_SESSION_UID(), -- proposed function
time_of_last_changes timestamp not null default current_timestamp
}

create function update_trigger_function() returns opaque as '
begin -- PG_UID() proposed function
if (select role from role where uid=PG_UID())=old.access then
new.time_of_last_changes=current_timestamp;
new.author_of_last_changes=PG_SESSION_UID(); -- proposed function
return new;
else
return null;
end if;
end;
' language 'plpgsql';

create trigger update_trigger before update on protected table for each row
execute procedure update_trigger_function();

Сertainly, I can create such function in my own project as:

create function pg_uid() returns integer as '
select usesysid from pg_user where usename=current_user;
' language 'sql';

Or as C function:

long pg_uid()
{
return GetUserId();
}

But, IMHO, such fuction must be common.

--
Olleg Samoylov