Postgresql return text variable value

Question

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;

In SQL (and PL/pgSQL) string constants have to be enclosed in single quotes ' double quotes are for identifiers. Please read Identifiers and Key Words in the manual. — user330315
– user330315, Commented Mar 9, 2018 at 8:58

Mabu Kloesen · Accepted Answer · 2018-03-09 08:59:31Z

2

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

edited Mar 9, 2018 at 8:59

user330315

answered Mar 9, 2018 at 8:47

Mabu Kloesen

1,3687 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marcel Klein · Accepted Answer · 2018-03-09 09:09:03Z

0

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

answered Mar 9, 2018 at 9:09

Marcel Klein

93 bronze badges

Collectives™ on Stack Overflow

Postgresql return text variable value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related