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
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
