X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id AAFEC5287A for ; Thu, 16 Jun 2005 09:39:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32608-08 for ; Thu, 16 Jun 2005 12:39:08 +0000 (GMT) Received: from s001.mxhosting.nl (unknown [213.206.92.11]) by svr1.postgresql.org (Postfix) with ESMTP id B77C95283C for ; Thu, 16 Jun 2005 09:39:07 -0300 (ADT) Received: from pinbizz.xs4all.nl ([82.92.94.47] helo=[192.168.11.224]) by s001.mxhosting.nl with asmtp (Exim 4.40 (FreeBSD)) id 1Dit5u-0008J1-Ha for pgsql-sql@postgresql.org; Thu, 16 Jun 2005 14:03:38 +0200 Message-ID: <42B172F1.7050007@dltmedia.nl> Date: Thu, 16 Jun 2005 14:39:13 +0200 From: "M.D.G. Lange" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: Function does not return, but gives error.. References: <043E1EAD3B6.000000A7s.gnanavel@inbox.com> In-Reply-To: <043E1EAD3B6.000000A7s.gnanavel@inbox.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - s001.mxhosting.nl X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [26 6] X-AntiAbuse: Sender Address Domain - dltmedia.nl X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.133 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200506/186 X-Sequence-Number: 21787 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 > > > >