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

my bad: please forget my previous mail, I tested it with a filled group,
which is of course not empty...
the solution was correct, thanks!

Gnanavel Shanmugam wrote:

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