From: Gnanavel Shanmugam <s.gnanavel@inbox.com>
To: M.D.G. Lange <mlange@dltmedia.nl>
To: pgsql-sql@postgresql.org
Subject: Re: Function does not return, but gives error..
Date: Thu, 16 Jun 2005 04:34:56 -0800
Message-ID: <043E1EAD3B6.000000A7s.gnanavel@inbox.com> (raw)
In-Reply-To: <42B16FFF.8050009@dltmedia.nl>
> -----Original Message-----
> From: mlange@dltmedia.nl
> Sent: Thu, 16 Jun 2005 14:26:39 +0200
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Function does not return, but gives error..
>
> I have the following function to determine wether or not a user is
> member of a group, however I have a small problem with it:
> a group without members results in groupres being NULL (I have checked
> this), however
> IF groupres = NULL
> THEN
change it to
IF groupres is NULL
THEN
> ....
> END IF;
> is not trapped... I have tried to use array_upper(groupres,1) < 1 OR
> array_upper(groupres,1) = NULL
> yet, I get no message about it... It is just that I find this strange
> behaviour, I could find a way to work around this with the if before the
> loop:
>
> Anyone any idea?
>
> TIA,
> Michiel
> --- function is_in_group(name,name) ---
> CREATE OR REPLACE FUNCTION "public"."is_in_group" (name, name) RETURNS
> boolean AS
> $body$
> DECLARE
> userid INTEGER;
> groupres INTEGER[];
> username ALIAS FOR $1;
> groupname ALIAS FOR $2;
> BEGIN
> SELECT INTO userid usesysid FROM pg_user WHERE usename = $1;
>
> IF NOT FOUND
> THEN
> RETURN false; -- not a known user, so the user is not a member
> of the group
> END IF;
>
> SELECT INTO groupres grolist FROM pg_group WHERE groname = $2;
>
> IF NOT FOUND
> THEN
> RAISE WARNING 'Unknown group ''%''', $2;
> RETURN false;
> END IF;
>
> IF groupres = NULL
> THEN
> -- no members in the group, so this user is not member either
> RAISE WARNING 'Group ''%'' has no members.', $2;
> RETURN false;
> END IF;
> RAISE WARNING 'Groupres: %',groupres;
>
> IF array_lower(groupres,1) >= 1
> THEN
> FOR currentgroup IN
> array_lower(groupres,1)..array_upper(groupres,1) LOOP
> IF groupres[currentgroup] = userid
> THEN
> RETURN true;
> END IF;
> END LOOP;
> END IF;
>
> -- if we can get here, the user was not found in the group
> -- so we return false
>
> RETURN false;
> END;
> $body$
> LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;
> --- end function ---
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
with regards,
S.Gnanavel
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: s.gnanavel@inbox.com, mlange@dltmedia.nl
Subject: Re: Function does not return, but gives error..
In-Reply-To: <043E1EAD3B6.000000A7s.gnanavel@inbox.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox