Postgres 15

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 dbobjverwalt
(
  id integer  generated always as identity primary key,
  icountry            bigint,
  iregion             bigint,
  isubregion          bigint,
  itown               bigint
);

create table dbobjfref 
(
  id integer generated always as identity primary key,
  name text
);

create table dbtbladrg 
(
  id integer generated always as identity primary key,
  objverwalt_id int not null references dbobjverwalt,
  dbobjfref_id int references dbobjfref
);

create view v_dbtbladrg 
as
select v as objverwalt, 
       r as dbobjfref
from dbtbladrg adr
  join dbobjverwalt v on v.id = adr.objverwalt_id
  left join dbobjfref r on r.id = adr.dbobjfref_id
;  

CREATE TABLE

CREATE TABLE

CREATE TABLE

CREATE VIEW

create function create_dbtbladrg(p_verwalt dbobjverwalt, p_objref dbobjfref)
  returns int
as
$$
declare
   verwalt_id int;
   ref_id int;
   adrg_id int;
begin
  insert into dbobjverwalt (icountry, iregion, isubregion, itown)
  values (p_verwalt.icountry, p_verwalt.iregion, p_verwalt.isubregion, p_verwalt.itown)
  returning id into verwalt_id;

  insert into dbobjfref (name) 
  values (p_objref.name)
  returning id into ref_id;
  
  insert into dbtbladrg (objverwalt_id, dbobjfref_id)
  values (verwalt_id, ref_id)
  returning id into adrg_id;

  return adrg_id;
end;
$$
language plpgsql;

CREATE FUNCTION

select create_dbtbladrg(row(null, 1, 2, 3, 4)::dbobjverwalt, row(null, 'ref 1')::dbobjfref);
select create_dbtbladrg(row(null, 10, 20, 30, 40)::dbobjverwalt, row(null, 'ref 2')::dbobjfref);

create_dbtbladrg

1

SELECT 1

create_dbtbladrg

2

SELECT 1

select *
from v_dbtbladrg;

objverwalt
dbobjfref

(1,1,2,3,4)
(1,"ref 1")

(2,10,20,30,40)
(2,"ref 2")

SELECT 2