Re: dunction issue

 -------------- Original message ----------------------
From: "Alain Roger" <raf.news@gmail.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@comcast.net