Postgres 13

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 mytable(col1 text, col2 boolean, col3 boolean);

CREATE TABLE


CREATE OR REPLACE FUNCTION fun1(vja json[])
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   v json;
BEGIN
   FOREACH v IN ARRAY vja
   LOOP
      INSERT INTO mytable(col1, col2, col3)
      VALUES(v ->> 'col1', (v ->> 'col2')::bool, (v ->> 'col3')::bool);
   END LOOP;
END
$func$;

CREATE FUNCTION


SELECT fun1('{"{\"col1\": \"turow1@af.com\", \"col2\": false, \"col3\": true}","{\"col1\": \"xy2@af.com\", \"col2\": false, \"col3\": true}"}'::json[]);
TABLE mytable;


fun1






SELECT 1




col1
col2
col3


turow1@af.com
f
t

xy2@af.com
f
t



SELECT 2


CREATE OR REPLACE PROCEDURE proc1(vja json[])
  LANGUAGE sql AS
$proc$
INSERT INTO mytable  -- target column list redundant in this particular case
SELECT r.*
FROM   unnest(vja) v, json_populate_record(NULL::mytable, v) r
$proc$;

CREATE PROCEDURE


CALL proc1('{"{\"col1\": \"turow2@af.com\", \"col2\": false, \"col3\": true}","{\"col1\": \"xy2@af.com\", \"col2\": false, \"col3\": true}"}'::json[]);
TABLE mytable;

CALL




col1
col2
col3


turow1@af.com
f
t

xy2@af.com
f
t

turow2@af.com
f
t

xy2@af.com
f
t



SELECT 4


CREATE OR REPLACE PROCEDURE proc2(vja json)
  LANGUAGE sql AS
$proc$
INSERT INTO mytable
SELECT * FROM json_populate_recordset(NULL::mytable, vja);
$proc$;

CREATE PROCEDURE


CALL proc2('[
    {"col1": "turow3@af.com", "col2": false, "col3": true}, 
    {"col1": "xy2@af.com", "col2": false, "col3": true} 
    ]');
TABLE mytable;

CALL




col1
col2
col3


turow1@af.com
f
t

xy2@af.com
f
t

turow2@af.com
f
t

xy2@af.com
f
t

turow3@af.com
f
t

xy2@af.com
f
t



SELECT 6

col1	col2	col3
turow1@af.com	f	t
xy2@af.com	f	t
turow2@af.com	f	t
xy2@af.com	f	t
turow3@af.com	f	t
xy2@af.com	f	t