title: Postgres bulk INSERT function using JSON arguments
body: <p>Here's a <code>plpgsql</code> function for postgres <code>9.6</code>. It tries to <code>INSERT</code> a row, and if the insert doesn't fail (due to a key constraint violation), then it runs a few more commands.</p>
  
  <pre><code>CREATE FUNCTION foo(int, text, text)
    RETURNS void AS
  $$
  BEGIN
    INSERT INTO table1 (id, val1, val2) VALUES ($1, $2, $3) ON CONFLICT DO NOTHING;
    IF FOUND THEN
      INSERT INTO table2 (table1_id, val1) VALUES ($1, $2);
      UPDATE table3 SET (val2, time) = ($3, now()) WHERE table1_id = $1;
    END IF;
  END
  $$
  </code></pre>
  
  <p>This function processes a single record, but how could you modify it to process a batch of thousands of records?</p>
  
  <p>I found an <a href="https://stackoverflow.com/a/24350689/779159">answer</a>, which suggests to make each of the 3 function arguments an array. But is there a way to do it where I'm passing in arguments that more closely represent how the records would look in my application?</p>
  
  <p>For example, the ideal solution would be my application code calls <code>select foo($1)</code>, where the parameter <code>$1</code> is a <strong>JSON array of objects</strong>, where each inner object is a record to be inserted.</p>
  
  <pre><code>[ 
    { "id": "1", "val1": "1-val1", "val2": "1-val2" },
    { "id": "2", "val1": "2-val1", "val2": "2-val2" },
    { "id": "3", "val1": "3-val1", "val2": "3-val2" },
    { "id": "4", "val1": "4-val1", "val2": "4-val2" }
  ]
  </code></pre>
  
  <p>The second-best option would be my application code calls <code>select foo($1, $2, $3, $4)</code>,  where each parameter is a <strong>JSON object</strong> corresponding to a record to be inserted.</p>
  
  <pre><code>{ "id": "1", "val1": "1-val1", "val2": "1-val2" }  // This would be $1
  { "id": "2", "val1": "2-val1", "val2": "2-val2" }  // This would be $2
  </code></pre>
  
  <p>I'm looking at the various JSON functions offered by Postgres <a href="https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE" rel="nofollow noreferrer">here</a> and they seem relevant to this but I can't figure out which exactly to use. Is what I'm looking to do even possible? Would using <strong>JSON arrays</strong> instead of <strong>JSON objects</strong> anywhere make this possible?</p>