CODEWITHSUNDEEP
postgresqlplpgsql
Hermes
Hermes

Reputation: 462

Postgresql return text variable value

I created a function. Firstly I declared a variable When a result return I want to return variable result but I get an error. How can I solve?

Error

ERROR: column "exist-user" does not exist

CREATE OR REPLACE FUNCTION savepromotionadmin(
     IN p_first_name character varying,
     IN p_last_name character varying,
     IN p_gsm character varying,
     IN p_email character varying,
     IN p_password character varying,
     IN p_level smallint)
  RETURNS text AS
$BODY$      
    DECLARE 
        result text;
        p_user_id bigint=(select id from user where email=p_email and gsm=p_gsm);
        p_check_user bigint=(select id from promotion_admin where email=p_email and gsm=p_gsm);
    BEGIN                   
        IF p_user_id is not null THEN
            IF p_check_user is null THEN            
                insert into promotion_admin(user_id, first_name, last_name, gsm, email, level, password)
                    values(p_user_id, p_first_name, p_last_name, p_gsm, p_email, p_level, md5(p_password));
                IF found THEN 
                    result:="added";                    
                    return result;
                ELSE 
                    result:="error";
                    return result;
                END IF;             
            ELSE 
                result:="exist-user";
                return result;
            END IF; 
        ELSE 
            result:="error";
            return result;
        END IF; 
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;

Upvotes: 0

Views: 5699

Answers (2)

Marcel Klein
Marcel Klein

Reputation: 9

The " are only for object-names (tables, columns etc.), for text you have to use '

change result:="added"; to result:='added'; and everything is fine

Upvotes: 0

Mabu Kloesen
Mabu Kloesen

Reputation: 1358

When you assign a data to a variable, you use the quote literal sign '. The quote indent sign " is for specify name of database object such as database name, table name, column name.

So, the code result:="exist-user" will assign data of column exist-user to variable result, and of course that column is not exists.

You can read more here

Upvotes: 2

Related Questions