> -----Original Message----- > From: [EMAIL PROTECTED] > 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq