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)