Handy user/group hack
Kind people,
Here's something I came up with for finding whether a PostgreSQL 7.4
user is in a group.
Cheers,
D
CREATE OR REPLACE FUNCTION in_group (text, text) RETURNS BOOLEAN AS '
DECLARE
the_user ALIAS FOR $1;
the_group ALIAS FOR $2;
dummy text; -- SELECT INTO dummy because PERFORM always returns true.
-- Is this a bug?
BEGIN
SELECT INTO dummy u.usename
FROM
pg_user u
, pg_group g
WHERE
u.usename = the_user
AND g.groname = the_group
AND u.usesysid = ANY (g.grolist);
IF FOUND
THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql' STRICT IMMUTABLE;
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778
Here's a slightly condensed version - do SQL functions have an advantage in
that they can be inlined? Or have I misunderstood?
CREATE OR REPLACE FUNCTION user_in_group(name,name) RETURNS boolean STRICT
AS '
SELECT EXISTS(SELECT u.* FROM pg_catalog.pg_user u INNER JOIN
pg_catalog.pg_group g ON (u.usesysid = ANY(g.grolist)) WHERE u.usename = $1
AND g.groname = $2);
' LANGUAGE 'SQL';
"David Fetter" <david@fetter.org> wrote in message
news:20031121040927.GJ6395@fetter.org...
Show quoted text
Kind people,
Here's something I came up with for finding whether a PostgreSQL 7.4
user is in a group.Cheers,
DCREATE OR REPLACE FUNCTION in_group (text, text) RETURNS BOOLEAN AS '
DECLARE
the_user ALIAS FOR $1;
the_group ALIAS FOR $2;
dummy text; -- SELECT INTO dummy because PERFORM always returns true.
-- Is this a bug?
BEGIN
SELECT INTO dummy u.usename
FROM
pg_user u
, pg_group g
WHERE
u.usename = the_user
AND g.groname = the_group
AND u.usesysid = ANY (g.grolist);IF FOUND
THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql' STRICT IMMUTABLE;
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings