title: How can I insert data using a plpgsql function?
body: <p>I'm trying to insert data into a table using a plpgsql function or stored procedure. However, I want to insert multiple records at once. I now these will all be VARCHAR so I thought I could use a function formed like <code>function(tablename VARCHAR, records VARCHAR[][])</code>. But then I found out multidimensional array support in plpgsql isn.t quite so fantastic.</p>
  
  <p>This is what my function looks like at the moment. This doesn't produce the result I'm looking for.
  When I ask</p>
  
  <pre><code>SELECT insert_data('tennis', ARRAY[ARRAY['1','2'], ARRAY['3','4']])
  </code></pre>
  
  <p>I get the following error</p>
  
  <pre><code>ERROR:  syntax error at or near "{"
  LINE 1: INSERT INTO tennis VALUES (null, {{1}}), (null, {{3}});
                                           ^
  QUERY:  INSERT INTO tennis VALUES (null, {{1}}), (null, {{3}});
  CONTEXT:  PL/pgSQL function "insert_data" line 26 at EXECUTE statement
  </code></pre>
  
  <p>However I am expecting a query like</p>
  
  <pre><code>INSERT INTO tennis VALUES (null, '1', '2'), (null, '3', '4');
  </code></pre>
  
  <p>which would work because table tennis has this structure.</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION insert_data (dsetname_in VARCHAR, records VARCHAR[][])
  RETURNS BOOLEAN AS $PROC$
  DECLARE
      insertquery TEXT;
      val VARCHAR;
      i INT;
      j INT;
  BEGIN
      insertquery = $$INSERT INTO $$ || dsetname_in || $$ VALUES $$;
      FOR i IN array_lower(records, 1)..array_upper(records, 1)
      LOOP
          insertquery = insertquery || $$(null, $$;
          FOR j IN array_lower(records[i:i], 1)..array_upper(records[i:i], 1)
          LOOP
              val = records[i:i][j:j];
              insertquery = insertquery || val;
              IF j &lt;&gt; array_upper(records[i:i], 1) THEN
                  insertquery = insertquery || $$, $$;
              END IF;
          END LOOP;
          insertquery = insertquery || $$)$$;
          IF i &lt;&gt; array_upper(records, 1) THEN
              insertquery = insertquery || $$, $$;
          END IF;
      END LOOP;
      insertquery = insertquery || $$;$$;
      EXECUTE insertquery;
      RETURN TRUE;
  END;$PROC$ LANGUAGE 'plpgsql';
  </code></pre>