-------------- Original message ----------------------
From: "Alain Roger" <raf.news@xxxxxxxxx>
> 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@xxxxxxxxxxx
--- Begin Message ---
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
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
--- End Message ---
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
