Thread: Problem with some function

Hi!

i create some function like this

CREATE FUNCTION checkuser(varchar,varchar) RETURNS INTEGER AS '
DECLARE
   user ALIAS FOR $1;
   pwd ALIAS FOR $2;
BEGIN
   SELECT id_user FROM users WHERE username=user AND password=pwd AND allow_login = ''true'';
   IF id_user IS NULL THEN
      RAISE NOTICE ''User not found'';
      RETURN 0;
   ELSE
      UPDATE users SET last_login = ''NOW'', login_counts = login_counts+1;
      RETURN id_user;
   END IF;
END;
' LANGUAGE 'plpgsql';

What i want this function is to do? Check is username and pass is
correct and if it is, then update table users with last_login date and
counts this login.


WHen i execute this

devDbSir=# SELECT checkuser('uros','mypass');

i get this error.

ERROR:  unexpected SELECT query in exec_stmt_execsql()

what am i doing wrong, please help me with this.


P.S. executin SELECT id_user...... myself it works. also update
sentence.
--
by,
 Uros

On Sun, 28 Oct 2001, Uros Gruber wrote:

> Hi!
>
> i create some function like this
>
> CREATE FUNCTION checkuser(varchar,varchar) RETURNS INTEGER AS '
> DECLARE
>    user ALIAS FOR $1;
>    pwd ALIAS FOR $2;
     id integer;
> BEGIN
    SELECT INTO id id_user FROM users WHERE username=user AND password=pwd
AND allow_login = ''true'';
-- You need to select into a variable here.

    IF id IS NULL THEN
>       RAISE NOTICE ''User not found'';
>       RETURN 0;
>    ELSE
>       UPDATE users SET last_login = ''NOW'', login_counts = login_counts+1;
-- Is this above really what you want?  You're not specifying the user to
-- update.  I think you probably want a WHERE on id or one like above.
       RETURN id;
>    END IF;
> END;
> ' LANGUAGE 'plpgsql';