Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE Points
(
    id serial,
    info character varying(20) NOT NULL,
    my_point geometry(Point,4326),
    CONSTRAINT points_pkey PRIMARY KEY (id)
)

ERROR:  type "geometry" does not exist
LINE 5:     my_point geometry(Point,4326),
                     ^


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->>'lat')::text::float8, 
                                 (points->>'lng')::text::float8), 
                                  4326), 
                        (points->>'info')::character varying
      FROM (SELECT json_array_elements(multiple_geom->'points') AS points) p;

    RETURN success;
END;

$BODY$;

CREATE FUNCTION


create table t (data json);

CREATE TABLE


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)

INSERT 0 1


select (points->>'lat')::text::float8 as lat,
       (points->>'lng')::text::float8 as lng,
       points->>'info' as info
from
     (select json_array_elements(data->'points') as points from t) p;


lat
lng
info


45.50884
-73.58781
x1

41.56355
-81.57327
x2

41.52335
-81.57423
x3



SELECT 3


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 $$;

ERROR:  relation "points" does not exist
LINE 1: INSERT INTO POINTS(my_point, info)
                    ^
QUERY:  INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point((points->>'lat')::text::float8, 
                                 (points->>'lng')::text::float8), 
                                  4326), 
                        (points->>'info')::character varying
      FROM (SELECT json_array_elements(multiple_geom->'points') AS points) p
CONTEXT:  PL/pgSQL function add_multiple_geom(json) line 7 at SQL statement
SQL statement "SELECT add_multiple_geom(data)"
PL/pgSQL function inline_code_block line 9 at PERFORM


SELECT * FROM Points;

ERROR:  relation "points" does not exist
LINE 1: SELECT * FROM Points;
                      ^