Mime-Version: 1.0
Date: Thu, 16 Jun 2005 04:34:56 -0800
Message-ID: <043E1EAD3B6.000000A7s.gnanavel@inbox.com>
From: Gnanavel Shanmugam <s.gnanavel@inbox.com>
Subject: Re: Function does not return, but gives error..
To: "M.D.G. Lange" <mlange@dltmedia.nl>, pgsql-sql@postgresql.org
In-Reply-To: <42B16FFF.8050009@dltmedia.nl>
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable

> -----Original Message-----
> From: mlange=40dltmedia.nl
> Sent: Thu, 16 Jun 2005 14:26:39 +0200
> To: pgsql-sql=40postgresql.org
> Subject: =5BSQL=5D 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 =3D 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) =3D 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 =22public=22.=22is_in_group=22 (name, name) =
RETURNS
> boolean AS
> =24body=24
> DECLARE
>        userid    INTEGER;
>        groupres  INTEGER=5B=5D;
>        username  ALIAS FOR =241;
>        groupname ALIAS FOR =242;
> BEGIN
>      SELECT INTO userid usesysid FROM pg_user WHERE usename =3D =241;
>
>      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 =3D =242;
>
>      IF NOT FOUND
>      THEN
>          RAISE WARNING 'Unknown group ''%''', =242;
>          RETURN false;
>      END IF;
>
>      IF groupres =3D NULL
>      THEN
>          -- no members in the group, so this user is not member either
>          RAISE WARNING 'Group ''%'' has no members.', =242;
>          RETURN false;
>      END IF;
>      RAISE WARNING 'Groupres: %',groupres;
>
>      IF array_lower(groupres,1) >=3D 1
>      THEN
>           FOR currentgroup IN
> array_lower(groupres,1)..array_upper(groupres,1) LOOP
>                IF groupres=5Bcurrentgroup=5D =3D 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;
> =24body=24
> 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=