Postgres variadic function to add multiple rows-CodePudding

I am trying to insert multiple rows into my table 'stuff' from an array. Below is my function. The problem I have is that by using the table stuff[] as the type of _stuff_array it is expecting a primary key as the initial value - as the stuff table has one. Is it possible to circumvent that? Or, is it possible to set the types to be expected in the array in the function declaration? If so, how?

Many thanks

Function definition:

CREATE OR REPLACE FUNCTION up_tbl_variadic(VARIADIC _stuff_array stuff[])
  RETURNS void
  LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO stuff        (
            X,
            Y
        )
      SELECT *
   FROM unnest(_stuff_array);
END;
$$;

Table definition:

CREATE TABLE IF NOT EXISTS stuff
(
    "id"            BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "X"   TEXT NOT NULL,
    "Y"    TEXT NOT NULL
)
WITH (
    OIDS = FALSE
)

CodePudding user response：

You need to only select the columns for which you want the values to be inserted:

CREATE OR REPLACE FUNCTION up_tbl_variadic(VARIADIC _stuff_array stuff[])
  RETURNS void
  LANGUAGE plpgsql AS
$$
BEGIN
  INSERT INTO stuff("X","Y")
  SELECT x, y
  FROM unnest(_stuff_array) as t(id, x, y);
END;
$$;

You declared the parameter as variadic so you shouldn't pass an array, but a list of records. And you need to create a three column record for each row you want to pass

So the following will insert two rows

SELECT up_tbl_variadic((null, 'stuff', 'morestuff'), 
                       (null, 'otherstuff', 'stuff again'));