Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table tree_header( tree_id integer generated always as identity 
                         , tree_type text
                        ); 
                        
create table tree_detail( dtl_id integer generated always as identity  
                        , tree_id integer
                        , owner_name text 
                        ); 
with parms (tree, owners) as
     ( values ('magnolia',  array ['Bill']) )
   , make_tree as
     ( insert into tree_header(tree_type)
         select tree from parms
         returning tree_id, tree_type
     )
insert into  tree_detail(owner_name, tree_id)
   select unnest (p.owners), mt.tree_id
     from parms p 
     join make_tree mt on (mt.tree_type = p.tree);   
1 rows affected

select * from tree_header; 
select * from tree_detail;


tree_id
tree_type


1
magnolia




dtl_id
tree_id
owner_name


1
1
Bill


-- or even multiples
with parms (tree, owners) as
     ( values ('spruce', array ['Susan','Rodger'])
            , ('cherry', array ['George','Tom','Linda','Sally','Ann'])
     )
   , make_tree as
     ( insert into tree_header(tree_type)
         select tree from parms
         returning tree_id, tree_type
     )
insert into  tree_detail(owner_name, tree_id)
   select unnest (p.owners), mt.tree_id
     from parms p 
     join make_tree mt on (mt.tree_type = p.tree);
7 rows affected

select * from tree_header; 
select * from tree_detail;


tree_id
tree_type


1
magnolia

2
spruce

3
cherry




dtl_id
tree_id
owner_name


1
1
Bill

2
2
Susan

3
2
Rodger

4
3
George

5
3
Tom

6
3
Linda

7
3
Sally

8
3
Ann