Re: 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(at)fetter(dot)org> wrote in message
news:20031121040927(dot)GJ6395(at)fetter(dot)org(dot)(dot)(dot)
> 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(at)fetter(dot)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
>