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)
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
