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 test_tab (
  field1 varchar(255), 
  field2 varchar(255), 
  field3 varchar(255), 
  field4 varchar(255)
)

CREATE TABLE


create or replace function test_function( input_string character varying )
  RETURNS void
  LANGUAGE plpgsql
  as $BODY$
  begin 
  create table test_xml_table as select input_string::xml as string_xml;
  insert into test_tab( field1, field2, field3, field4 ) 
    select xt.field1, xt.field2, xt.field3, field4 
    from test_xml_table, xmltable( '/header/data/line' passing string_xml
        columns 
           field1 text path 'field1', 
           field2 text path 'field2', 
           field3 text path 'field3', 
           field4 text path 'field4' 
    ) as xt;                                   
end;

$BODY$;

CREATE FUNCTION


select test_function( '<header>
    <data>
      <line>
         <field1>some data in f1</field1>
         <field2>other data f2</field2>
         <field3>this data contains numers 012323</field3>
         <field4>and the last data</field4>
       </line>              
     </data>                
</header>'   )


test_function






SELECT 1


select *
from test_tab;


field1
field2
field3
field4


some data in f1
other data f2
this data contains numers 012323
and the last data



SELECT 1