Message-ID: <42B16FFF.8050009@dltmedia.nl>
Date: Thu, 16 Jun 2005 14:26:39 +0200
From: "M.D.G. Lange" <mlange@dltmedia.nl>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Function does not return, but gives error..
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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