Function does not return, but gives error..
От | M.D.G. Lange |
---|---|
Тема | Function does not return, but gives error.. |
Дата | |
Msg-id | 42B16FFF.8050009@dltmedia.nl обсуждение исходный текст |
Ответы |
Re: Function does not return, but gives error..
Re: Function does not return, but gives error.. |
Список | pgsql-sql |
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 ---
В списке pgsql-sql по дате отправления: