Postgres Insert An Array without a Loop

Question

I am passing an array of values from Npgsql to a function that inserts multiple rows at a time, and returns the auto-assigned serial ids. The following is the code that I have working on the server side:

CREATE TABLE "MyTable" (
    "ID" SERIAL PRIMARY KEY,
    "Value" DOUBLE PRECISION NOT NULL
);

CREATE TYPE "MyTableType" AS (
    "Value" DOUBLE PRECISION
);

CREATE FUNCTION "InsertIntoMyTable"(entries "MyTableType"[])
    RETURNS SETOF INT AS $$

    DECLARE
        insertCmd TEXT := 'INSERT INTO "MyTable" ("Value") '
            'VALUES ($1) RETURNING "ID"';
        entry "MyTableType";
    BEGIN
        FOREACH entry IN ARRAY entries LOOP
            RETURN QUERY EXECUTE insertCmd USING entry."Value";
        END LOOP;
    END;
$$ LANGUAGE PLPGSQL;

My question is, is there some way to insert each array value as a row without looping over the array? For example, is there some Postgres syntax to make the following implementation possible:

CREATE FUNCTION "InsertIntoMyTable"(entries "MyTableType"[])
    RETURNS SETOF INT AS $$

    BEGIN
        INSERT INTO "MyTable" ("Value")
            SELECT "Value" FROM entries
            RETURNING "ID";
    END;
$$ LANGUAGE SQL;

The goal is to perform these inserts as fast as possible, as it is the most frequent task done by my production system.

Unrelated, but: you should really avoid quoted identifiers. They are much more trouble in the long run than they are worth it. — a_horse_with_no_name

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2015-04-16T17:28:14

You can use the unnest() function to convert the array into a set:

INSERT INTO "MyTable" ("Value")
SELECT i
from unnest(entries) i;

Postgres Insert An Array without a Loop

1 Answers