Re: Function does not return, but gives error..

> -----Original Message-----
> From: mlange(at)dltmedia(dot)nl
> Sent: Thu, 16 Jun 2005 14:26:39 +0200
> To: pgsql-sql(at)postgresql(dot)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