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

Thu, 20 Nov 2003 20:09:27 -0800
David Fetter <david@fetter.org>
Handy user/group hack
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...
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>



Sat, 22 Nov 2003 21:11:21 +0100
"Tom Hebbron" <news_user@hebbron.com>
Re: Handy user/group hack