Postgres Insert An Array without a Loop

Vote count:

0

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.

asked 57 secs ago

Jeff G

160

Postgres Insert An Array without a Loop