Postgres 11

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 cashreg_journal (
  id SERIAL PRIMARY KEY,
  gid INTEGER NOT NULL,
  device_id TEXT NOT NULL,
  item TEXT NOT NULL,
  vat_percentage INTEGER NOT NULL,
  vat_sum INTEGER, 
  price NUMERIC);
CREATE TYPE cr_journal AS (
  gid INTEGER,
  device_id TEXT,
  item TEXT,
  vat_percentage INTEGER,
  vat_sum INTEGER, 
  price NUMERIC);
CREATE OR REPLACE FUNCTION test_insert(daco TEXT, _journal_arr  cr_journal[]) RETURNS TEXT
  LANGUAGE plpgsql
  AS $function$

DECLARE

    cj cr_journal;

BEGIN

    FOREACH cj IN array _journal_arr LOOP
    
        INSERT INTO cashreg_journal
            (gid, device_id, item, vat_percentage, vat_sum, price)
        SELECT cj.*;
        
    END LOOP;

  RAISE NOTICE 'Daco: %', daco;

  RETURN 'Saved';

END;
$function$;
SELECT test_insert('Test daco', 
                   (ARRAY[(1, '000-111', 'Test-P', 20, 20, 100), 
                         (1, '000-111', 'Test-F', 20, 10, 100)])::cr_journal[]);
                         


test_insert


Saved


select * from cashreg_journal;


id
gid
device_id
item
vat_percentage
vat_sum
price


1
1
000-111
Test-P
20
20
100

2
1
000-111
Test-F
20
10
100