CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Jeff G
Jeff G

Reputation: 4677

Postgres Insert An Array without a Loop

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.

Upvotes: 1

Views: 991

Answers (1)

user330315
user330315

Reputation:

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

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

Upvotes: 3

Related Questions