pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFunction does not return, but gives error..
5+ messages / 3 participants
[nested] [flat]
* Function does not return, but gives error..
@ 2005-06-16 12:26 M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:34 ` Re: Function does not return, but gives error.. Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 12:47 ` Re: Function does not return, but gives error.. Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 5+ messages in thread
From: M.D.G. Lange @ 2005-06-16 12:26 UTC (permalink / raw)
To: pgsql-sql
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 ---
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function does not return, but gives error..
2005-06-16 12:26 Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
@ 2005-06-16 12:34 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 12:39 ` Re: Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:43 ` Re: Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 2 replies; 5+ messages in thread
From: Gnanavel Shanmugam @ 2005-06-16 12:34 UTC (permalink / raw)
To: M.D.G. Lange <mlange@dltmedia.nl>; pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function does not return, but gives error..
2005-06-16 12:26 Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:34 ` Re: Function does not return, but gives error.. Gnanavel Shanmugam <s.gnanavel@inbox.com>
@ 2005-06-16 12:39 ` M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 0 replies; 5+ messages in thread
From: M.D.G. Lange @ 2005-06-16 12:39 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function does not return, but gives error..
2005-06-16 12:26 Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:34 ` Re: Function does not return, but gives error.. Gnanavel Shanmugam <s.gnanavel@inbox.com>
@ 2005-06-16 12:43 ` M.D.G. Lange <mlange@dltmedia.nl>
1 sibling, 0 replies; 5+ messages in thread
From: M.D.G. Lange @ 2005-06-16 12:43 UTC (permalink / raw)
To: pgsql-sql
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
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Function does not return, but gives error..
2005-06-16 12:26 Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
@ 2005-06-16 12:47 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-06-16 12:47 UTC (permalink / raw)
To: M.D.G. Lange <mlange@dltmedia.nl>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-06-16 12:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-16 12:26 Function does not return, but gives error.. M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:34 ` Gnanavel Shanmugam <s.gnanavel@inbox.com>
2005-06-16 12:39 ` M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:43 ` M.D.G. Lange <mlange@dltmedia.nl>
2005-06-16 12:47 ` Michael Fuhr <mike@fuhr.org>
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