Thread: 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@fetter.org http://fetter.org/
phone: +1 510 893 6100    cell: +1 415 235 3778

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

david@fetter.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@sss.pgh.pa.us> wrote:
> david@fetter.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@fetter.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