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