title: Using variables in a PL/pgSQL function
body: <p><a href="https://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT" rel="nofollow noreferrer">Postgres PL/pgSQL docs say</a>:</p>
  
  <blockquote>
    <p>For any SQL command that does not return rows, for example <code>INSERT</code>
    without a <code>RETURNING</code> clause, you can execute the command within a
    PL/pgSQL function just by writing the command.</p>
    
    <p>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.</p>
  </blockquote>
  
  <p>But when I use variable names in my queries I get an error:</p>
  
  <blockquote>
  <pre><code>ERROR:  syntax error at or near "email"
  LINE 16: ...d,email,password) values(identity_id,current_ts,''email'',''...
  </code></pre>
  </blockquote>
  
  <p>This is my function:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Why when I use variables in the query, Postgres throws an error. How is this supposed to be written?</p>