I'm looking to do a multiple dynamic insert with a variable array/list. The number of inserts will always vary.
I'd like to replace
insert into my table (col 1, col 2)
select *
from unnest(array[1, 2], array[3, 4]);
with
insert into my table (col 1, col 2)
select *
from unnest(:list-of-inserts);
Depends if you want to create a new Table with your data or not ?
This could be a simple :
SELECT * INTO table FROM unnest(array[1, 2], array[3, 4]);
OR
Do the same in a temporary table :
SELECT * INTO temp mytable FROM unnest(array[1, 2], array[3, 4]);
Then
INSERT INTO table (col 1, col 2) SELECT col1, col2 FROM mytable;
Hope this will answer your question.
I'd wrap it in a function... this will then translate nicely into any programming language that has a data adapter for PostgreSQL, which is pretty much all of them. As an added bonus, it will work great in psql also.
CREATE OR REPLACE FUNCTION ez_insert(list1 integer[], list2 integer[])
RETURNS integer AS
$BODY$
DECLARE
rowcount integer;
BEGIN
insert into my_table (col_1, col_2)
values
(unnest (list1), unnest (list2));
GET DIAGNOSTICS rowcount = ROW_COUNT;
return rowcount;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
Invoking:
select ez_insert (array[1, 2, 3], array[7, 8, 9])
Caveat -- if your arrays are different sizes, expect weirdness.
Here is a quick example of an implementation in C#:
NpgsqlCommand cmd = new NpgsqlCommand("select ez_insert(:ARRAY1, :ARRAY2)", conn);
cmd.Parameters.Add(new NpgsqlParameter("ARRAY1",
NpgsqlDbType.Array | NpgsqlDbType.Integer));
cmd.Parameters.Add(new NpgsqlParameter("ARRAY2",
NpgsqlDbType.Array | NpgsqlDbType.Integer));
cmd.Parameters[0].Value = ar1;
cmd.Parameters[1].Value = ar2;
int inserts = (int)cmd.ExecuteScalar();
