Using variables in a PL/pgSQL function

Question

Postgres PL/pgSQL docs say:

For any SQL command that does not return rows, for example INSERT without a RETURNING clause, you can execute the command within a PL/pgSQL function just by writing the command.

Any PL/pgSQL variable name appearing in the command text is treated as a parameter, and then the current value of the variable is provided as the parameter value at run time.

But when I use variable names in my queries I get an error:

ERROR:  syntax error at or near "email"
LINE 16: ...d,email,password) values(identity_id,current_ts,''email'',''...

This is my function:

CREATE OR REPLACE FUNCTION app.create_identity(email varchar,passwd varchar)
RETURNS integer as $$
DECLARE
    current_ts          integer;
    new_identity_id     integer;
    int_max             integer;
    int_min             integer;
BEGIN
    SELECT extract(epoch FROM now())::integer INTO current_ts;
    int_min:=-2147483648;
    int_max:= 2147483647;
    LOOP
        BEGIN
            SELECT floor(int_min + (int_max - int_min + 1) * random()) INTO new_identity_id;
            IF new_identity_id != 0 THEN
                INSERT into app.identity(identity_id,date_inserted,email,password) values(identity_id,current_ts,''email'',''passwd'');
                RETURN new_identity_id;
            END IF;
        EXCEPTION
            WHEN unique_violation THEN
        END;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Why when I use variables in the query, Postgres throws an error. How is this supposed to be written?

Answer 1

You can not put the parameter names in single quotes (''email'' and you can't use the parameter email "as is" because it has the same name as a column in the table. This name clash is one of the reasons it is highly recommended to not use variables or parameters that have the same name as a column in one of the tables. You have three choices to deal with this:

1. rename the variable. A common naming convention is to prefix parameters with p_ e.g. p_email, then use the un-ambigous names in the insert

   INSERT into app.identity(identity_id,date_inserted,email,password) 
   values(identity_id,current_ts,p_email,p_password);
   

2. use the $1 for the first parameter and $2 for the second:

   INSERT into app.identity(identity_id,date_inserted,email,password) 
   values(identity_id,current_ts,$1,$2);
   

3. prefix the parameter name with the function name:

   INSERT into app.identity(identity_id,date_inserted,email,password) 
   values(identity_id,current_ts,create_identity.email,create_identity.password);
   

I would highly recommend to go with option 1

---

Unrelated, but: you don't need SELECT statements to assign variable values if you don't retrieve those values from a table.

SELECT extract(epoch FROM now())::integer INTO current_ts;

can be simplified to:

current_ts := extract(epoch FROM now())::integer;

and

SELECT floor(int_min + (int_max - int_min + 1) * random()) INTO new_identity_id;

to

new_identity_id := floor(int_min + (int_max - int_min + 1) * random());