How to insert into table multiple geometry rows from function JSON param? My table:

CREATE TABLE Points
(
    id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass),
    info character varying(20) NOT NULL,
    my_point geometry(Point,4326),
    CONSTRAINT points_pkey PRIMARY KEY (id)
)


JSON text:

{
  "points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}
  ]
}


My function:

CREATE OR REPLACE FUNCTION add_multiple_geom ( multiple_geom json )
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point(lng,lat),4326), info;                                                              

  RETURN success;
END;

$BODY$;
You can unnest the array values using json_array_elements function and -&gt;&gt; operator:

create table t (data json);

insert into t 
values ('{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json);

select points-&gt;&gt;'lat' as lat,
       points-&gt;&gt;'lng' as lng,
       points-&gt;&gt;'info' as info
from
     (select json_array_elements(data-&gt;'points') as points
      from t) p;





lat      | lng       | info
:------- | :-------- | :---
45.50884 | -73.58781 | x1;
41.56355 | -81.57327 | x2;
41.52335 | -81.57423 | x3;


db&lt;>fiddle here

Now change your function by:

CREATE OR REPLACE FUNCTION add_multiple_geom (multiple_geom json)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point((points-&gt;&gt;'lat')::text::float8, 
                                 (points-&gt;&gt;'lng')::text::float8), 
                                  4326), 
                        (points-&gt;&gt;'info')::character varying
      FROM (SELECT json_array_elements(multiple_geom -&gt;'points') AS points) p;

    RETURN success;
END;

$BODY$;


And try to use it:

DO $$
DECLARE data JSON;
BEGIN
    data := '{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json;

    PERFORM add_multiple_geom(data);
END $$;




SELECT * FROM Points;



id | info | my_point                                          
-: | :--- | :-------------------------------------------------
 1 | x1   | 0101000020E6100000C5724BAB21C14640C190D5AD9E6552C0
 2 | x2   | 0101000020E610000095D4096822C84440C971A774B06454C0
 3 | x3   | 0101000020E6100000492EFF21FDC2444074982F2FC06454C0


db&lt;>fiddle here