Re: PERFORM bug with FOUND?

Kind people,

I've written a little hack, here included, which is supposed to find
whether a user is in a group. I'd intended to do a PERFORM instead of
SELECT INTO, but PERFORM appears to set FOUND to true no matter what.
What's going on here?

The hack in question...

CREATE OR REPLACE FUNCTION in_group (text, text) RETURNS BOOLEAN AS '
DECLARE
the_user ALIAS FOR $1;
the_group ALIAS FOR $2;
dummy text; -- SELECT INTO dummy because PERFORM always returns true.
-- Is this a bug?
BEGIN
SELECT INTO dummy u.usename
FROM
pg_user u
, pg_group g
WHERE
u.usename = the_user
AND g.groname = the_group
AND u.usesysid = ANY (g.grolist);

IF FOUND
THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

TIA for any hints, tips or pointers :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778

If you don't vote, don't bitch.

david(at)fetter(dot)org (David Fetter) writes:
> I've written a little hack, here included, which is supposed to find
> whether a user is in a group. I'd intended to do a PERFORM instead of
> SELECT INTO, but PERFORM appears to set FOUND to true no matter what.

What version are you using? It seems to work per spec in 7.4.

regression=# create function foo(name) returns bool as '
regression'# begin
regression'# perform * from pg_user where usename = $1;
regression'# return found;
regression'# end' language plpgsql;
CREATE FUNCTION
regression=# select foo('postgres');
foo
-----
t
(1 row)

regression=# select foo('not');
foo
-----
f
(1 row)

regression=#

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> david(at)fetter(dot)org (David Fetter) writes:
>> I've written a little hack, here included, which is supposed to find
>> whether a user is in a group. I'd intended to do a PERFORM instead of
>> SELECT INTO, but PERFORM appears to set FOUND to true no matter what.
>
> What version are you using? It seems to work per spec in 7.4.
>
> regression=# create function foo(name) returns bool as '
> regression'# begin
> regression'# perform * from pg_user where usename = $1;
> regression'# return found;
> regression'# end' language plpgsql;
> CREATE FUNCTION
> regression=# select foo('postgres');
> foo
> -----
> t
> (1 row)
>
> regression=# select foo('not');
> foo
> -----
> f
> (1 row)
>
> regression=#
>
> regards, tom lane

Tom,

Thanks for staying on top of this. This was 7.4 on Linux i686,
compiling from source with just the defaults except port. Here's a
working version.

CREATE OR REPLACE FUNCTION in_group (text, text) RETURNS BOOLEAN AS '
BEGIN
PERFORM u.usename
FROM
pg_user u
, pg_group g
WHERE
u.usename = $1
AND g.groname = $2
AND u.usesysid = ANY (g.grolist);

RETURN FOUND;
END;
' LANGUAGE 'plpgsql' STRICT IMMUTABLE;

BTW, I'd like to lobby for an example in the docs of how to do a
PERFORM instead of a SELECT, 'cause that syntax wasn't right away
obvious.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778

All for ourselves, and nothing for other people, seems, in every age
of the world, to have been the vile maxim of the masters of mankind.
Adam Smith,
An Inquiry Into the Nature and Causes of the Wealth of Nations