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

Gnanavel Shanmugam wrote:

>>-----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
>  
>
Thanks for the quick response, however that does not help either...

>  
>
>>....
>>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
>
>
>  
>