Postgres 14

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 demo(id integer);

CREATE TABLE

SELECT * FROM demo;

id

SELECT 0

CREATE OR REPLACE procedure mProcedure(INOUT resp json DEFAULT NULL::JSON)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
field_data json := '{ "fields": [1,2,3,4,5] }';
_field_id int;
BEGIN

-- Start of block X
FOR _field_id IN SELECT * FROM json_array_elements(json_extract_path(field_data,'fields'))
LOOP
    INSERT INTO demo VALUES(_field_id);
    
    IF(_field_id = 3) THEN
      RAISE EXCEPTION USING ERRCODE='22013';
    END IF;
   
    IF(_field_id = 5) THEN
      RAISE EXCEPTION USING ERRCODE='22014';
    END IF;
END LOOP;

SELECT json_agg(row_to_json(d)) INTO resp FROM demo d;
RETURN;
-- end of block X

-- if an exception occurs in block X, then all the changes made within the block are rollback
-- and the control is passed on to the EXCEPTION WHEN OTHERS block.  
EXCEPTION 
WHEN sqlstate '22013' THEN
resp := json_build_object('code',26);

WHEN sqlstate '22014' THEN
resp := json_build_object('code',22);

END;
$BODY$;

CREATE PROCEDURE

CALL mProcedure();

resp

{"code" : 26}

CALL

SELECT * FROM demo;

id

SELECT 0