From: M.D.G. Lange <mlange@dltmedia.nl>
To: pgsql-sql@postgresql.org
Subject: Function does not return, but gives error..
Date: Thu, 16 Jun 2005 14:26:39 +0200
Message-ID: <42B16FFF.8050009@dltmedia.nl> (raw)
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 ---
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: <42B16FFF.8050009@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