Re: dunction issue

-------------- Original message ----------------------
From: "Alain Roger" <raf(dot)news(at)gmail(dot)com>
> Hi,
>
> i have a problem solving my function trouble.
> this function should return an email address stored in a table
> (tmp_newsletterreg) based on a number (sessions ID).
> if the session id is not find it should return a string corresponding to and
> error.
> if the email in found but already exists into another table (users), it
> should also return a string value relative the this error.
>
> here is my stored procedure.
>
> > CREATE OR REPLACE FUNCTION cust_portal.sp_u_002(id_session character
> > varying)
> > RETURNS character varying AS
> > $BODY$
> >
> > DECLARE
> >
> > ret_email CHARACTER VARYING(512) :='';
> > usr_exists INTEGER := 0;
> > usr_exists_2 INTEGER := 0;
> >
> > BEGIN
> > set search_path = cust_portal;
> >
> > SELECT count(*) INTO usr_exists FROM tmp_newsletterreg WHERE
> > tmp_usr_id = id_session;
> > IF (usr_exists = 1) THEN
> > SELECT email INTO ret_email FROM tmp_newsletterreg WHERE
> > tmp_usr_id = id_session;
> > IF (ret_email IS NULL || ret_email='') THEN

See if I can do better this time. I believe your problem is here:
IF (ret_email IS NULL || ret_email='') THEN
|| is the string concatenation operator. If you are trying to test both cases then you need to do something along lines of
IF (ret_email IS NULL ) THEN
RETURN ('-3')
ELSIF (ret_email='')
RETURN ('-3')

> > RETURN ('-3');
> > ELSE
> > SELECT count(*) INTO usr_exists_2 FROM users WHERE users.email=
> ret_email;
> > IF (usr_exists_2 = 0) THEN -- first try of user to get
> > registered
> > RETURN (ret_email);
> > ELSE -- user already exists into users tables (several
> > tries to register)
> > RETURN ('-2');
> > END IF;
> > END IF;
> > ELSE
> > RETURN('-1');
> > END IF;
> > END;
> >
>
> if the session id is wrong, it works correctly.
> however if the session id is correct it does not return me the email address
> (even if it really exist into table tmp_newsletterreg / but not in table
> users.)
> so i think my eyes are tired, because i do not see an error...
>
> thanks.
> --
> Alain
> ------------------------------------
> Windows XP SP2
> PostgreSQL 8.2.4 / MS SQL server 2005
> Apache 2.2.4
> PHP 5.2.4
> C# 2005-2008

--
Adrian Klaver
aklaver(at)comcast(dot)net