From: M.D.G. Lange <mlange@dltmedia.nl>
To: pgsql-sql@postgresql.org
Subject: Re: Function does not return, but gives error..
Date: Thu, 16 Jun 2005 14:43:10 +0200
Message-ID: <42B173DE.8010006@dltmedia.nl> (raw)
In-Reply-To: <043E1EAD3B6.000000A7s.gnanavel@inbox.com>
References: <043E1EAD3B6.000000A7s.gnanavel@inbox.com>
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
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mlange@dltmedia.nl
Subject: Re: Function does not return, but gives error..
In-Reply-To: <42B173DE.8010006@dltmedia.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox