Обсуждение: 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
...
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 ---
> -----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 > .... > 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
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 > > > >
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@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 > > > > >>.... >>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 > > > >
On Thu, Jun 16, 2005 at 02:26:39PM +0200, M.D.G. Lange wrote: > > IF groupres = NULL > THEN > ... > END IF; > is not trapped... Be sure to understand how NULL works in comparisons: http://www.postgresql.org/docs/8.0/static/functions-comparison.html SELECT NULL = NULL;?column? ---------- (1 row) SELECT (NULL = NULL) IS TRUE;?column? ----------f (1 row) SELECT (NULL = NULL) IS FALSE;?column? ----------f (1 row) SELECT (NULL = NULL) IS NULL;?column? ----------t (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/