Grouped result insert into two tables

Question

I have the following table.

CREATE TEMPORARY TABLE temp_detail
(
  purchase_order_detail_id INTEGER,
  item_id integer,
  qty numeric(18,2),
  project_id integer,
  category_id integer,
  supplier_id integer,
  rate numeric(18,2)
);

I am getting the grouped result with ids using

  SELECT array_agg(purchase_order_detail_id), project_id, category_id, supplier_id 
  FROM temp_detail 
  GROUP BY project_id, category_id, supplier_id

Now I want to insert project_id, category_id, supplier_id into a master table and item_id,qty,rate into its detail table. Detail table will have the master table id as foreign key. Please help.

Answer 1

Assuming this schema:

create table master (
    master_id serial primary key,
    project_id int,
    category_id int,
    supplier_id int
);
create table detail (
    detail_id int,
    item_id int,
    qty numeric(18,2),
    rate numeric(18,2),
    master_id int references master (master_id)
);
create temporary table temp_detail (
    purchase_order_detail_id integer,
    item_id integer,
    qty numeric(18,2),
    project_id integer,
    category_id integer,
    supplier_id integer,
    rate numeric(18,2)
);

This will do it:

with d as (
    insert into master (project_id, category_id, supplier_id)
    select distinct project_id, category_id, supplier_id
    from temp_detail
    returning *
)
insert into detail (item_id, qty, rate, master_id)
select item_id, qty, rate, master_id
from
    temp_detail td
    inner join
    d on (td.project_id, td.category_id, td.supplier_id) = (d.project_id, d.category_id, d.supplier_id)
;