title: How to do variable substitution in plpgsql?
body: <p>I've got a bit of complex sql code that I'm converting from MSSql to Postgres (using Entity Framework Core 2.1), to deal with potential race conditions when inserting to a table with a unique index. Here's the dumbed-down version:</p>
  
  <pre><code>const string QUERY = @"
  DO
  $$
      BEGIN
          insert into Foo (Field1,Field2,Field3)
          values (@value1,@value2,@value3);
      EXCEPTION WHEN others THEN
          -- do nothing; it's a race condition
      END;
  $$ LANGUAGE plpgsql;
  
  select *
  from Foo
  where Field1 = @value1
  and Field2 = @value2;
  ";
  
  return DbContext.Foos
                  .FromSql(QUERY,
                      new NpgsqlParameter("value1", value1),
                      new NpgsqlParameter("value2", value2),
                      new NpgsqlParameter("value3", value3))
                  .First();
  </code></pre>
  
  <p>In other words, try to insert the record, but don't throw an exception if the attempt to insert it results in a unique index violation (index is on Field1+Field2), and return the record, whether it was created by me or another thread.</p>
  
  <p>This concept worked fine in MSSql, using a <code>TRY..CATCH</code> block. As far as I can tell, the way to handle Postgres exceptions is as I've done, in a plpgsql block.</p>
  
  <p><strong>BUT...</strong></p>
  
  <p>It appears that variable substitution in plpgsql blocks doesn't work. The code above fails on the <code>.First()</code> (no elements in sequence), and when I comment out the <code>EXCEPTION</code> line, I see the real problem, which is:</p>
  
  <blockquote>
    <p>Npgsql.PostgresException : 42703: column "value1" does not exist</p>
  </blockquote>
  
  <p>When I test using regular Sql, i.e. doing the <code>insert</code> without using a plpgsql block, this works fine.</p>
  
  <p>So, what is the correct way to do variable substitution in a plpgsql block?</p>