Insert json as a record to PostgreSQL table-CodePudding

I have a table in Postgres that looks something like:

TABLE Product (
    id serial,
    name varchar(512),
    price numeric,
    ...
)

I am trying to create a procedure that would take an array of json objects and insert each element as a row into the table.

I am doing something like this:

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql AS $$
   DECLARE _elem json;
   BEGIN
    FOREACH _elem IN ARRAY entries
      LOOP 
        INSERT INTO product
           SELECT id, name, price
           FROM json_populate_record (NULL::product,
             format('{
                "id": "%d",
                "name": "%s",
                "price": "%f"
               }', _elem->id, _elem->name, _elem->price)::json
      );
      END LOOP;
END;
$$

And I'm getting an error "column "id" does not exist"

Is the problem here that id is serial, and in my json object it's an int? What's the right way to insert row from a json object here?

CodePudding user response：

Your _elem variable is already a JSON value, so you can pass that directly to the json_populate_record() function:

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE plpgsql 
AS $$
DECLARE 
  _elem json;
BEGIN
  FOREACH _elem IN ARRAY entries
  LOOP 
    INSERT INTO product
    SELECT id, name, price
    FROM json_populate_record (NULL::product, _elem);
  END LOOP;
END;
$$
;

But you don't need a LOOP or even PL/pgSQL for this:

CREATE OR REPLACE PROCEDURE insert_into_product(entries json[]) 
LANGUAGE sql
AS $$
  INSERT INTO product
  SELECT (json_populate_record(null::product, u.entry)).*
  FROM unnest(entries) as u(entry);
$$
;