Re: Help with a very newbie question...

On Feb 23, 2005, at 6:14 AM, Andre Schnoor wrote:

>
> CREATE sp_getuser(name, pass) RETURNS record AS
> $body$
> DECLARE
>     retval RECORD;
> BEGIN
>     SELECT INTO retval * FROM Users WHERE userid=name AND passwd=pass;
>     IF NOT FOUND THEN
>         RETURN NULL;
>     ELSE
>         RETURN retval;
> END;
> $body$
> LANGUAGE plpgsql;
>
>

Almost....

First, note the declaration for the function--slightly different
arguments.  Then, note the declare section--two new variables there to
replace those in the arguments.  You need to END IFs everywhere.
Otherwise, looks good.  Test given below:

create table users (
    userid varchar,
    passwd varchar);
CREATE TABLE
insert into users values('joe','joepass');
INSERT 156196622 1
insert into users values('susan','susanpass');
INSERT 156196623 1
CREATE OR REPLACE FUNCTION sp_getuser(varchar,varchar) RETURNS record
AS $$
    DECLARE
         retval RECORD;
         name_lu ALIAS FOR $1;
         pass_lu ALIAS FOR $2;
    BEGIN
         SELECT INTO retval * FROM Users WHERE userid=name_lu AND
passwd=pass_lu;
         IF NOT FOUND THEN
             RETURN NULL;
            ELSE
             RETURN retval;
         END IF;
    END;
$$
LANGUAGE plpgsql;
CREATE FUNCTION
select sp_getuser('joe','joepass');
   sp_getuser
---------------
  (joe,joepass)
(1 row)

select sp_getuser('joe','notjoepass');
  sp_getuser
------------

(1 row)