Insert/Update to multiple tables with relations

["Dave" <withheld@xxxxxxxxxxxxx>]

Hi,

I need help with designing a set of queries I am running with psql -f 
reports.sql

I have a tb_master with report headers, tb_records table with it's own 
serial field and foreign key referencing an "id" of tb_master. The third 
tb_details table has two foreign keys referencing the "id"'s of both, 
tb_master, and tb_records. Below is a simplistic representation of those 
three tables:

CREATE TABLE "sch_reports"."tb_master" (
  "id" SERIAL,
 "some_ref" VARCHAR
 "some_text" VARCHAR
) WITH OIDS;

CREATE TABLE "sch_reports"."tb_records" (
  "id" SERIAL,
  "master_id" INTEGER NOT NULL,
  "some_text" VARCHAR,
CONSTRAINT "fk_master_id" FOREIGN KEY ("master_id")
    REFERENCES "sch_reports"."tb_master"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITH OIDS;

CREATE TABLE "sch_reports"."tb_details" (
  "master_id" BIGINT NOT NULL,
  "record_id" INTEGER NOT NULL,
  "some_text" VARCHAR NOT NULL,
  CONSTRAINT "fk_record_id" FOREIGN KEY ("record_id")
    REFERENCES "sch_reports"."tb_records"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE,
CONSTRAINT "fk_master_id" FOREIGN KEY ("master_id")
    REFERENCES "sch_reports"."tb_master"("id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITH OIDS;

During update/insert a single row is inserted in tb_master, representing a 
single report.
In the same transactiom I would like to enter multiple rows (report line 
items) into tb_records. A new record should get a new id, and foreign key of 
the tb_master. If rows with  should USE the id of tb_master and update all 
matching rows.
In the same transaction I would like to enter multiple rows into tb_details. 
A new record will need to have two foreign keys referencing above two tables 
serial "id" rows.
I'm using a function to insert/update tb_master and here is what I have so 
far, but I'm having trouble getting/setting appropriate row id's to insert 
records for the other two table in the same transaction. I realize I can use 
some_ref of tb_master to handle the relations, but I'd like to try with 
serial id's first. Aslo, I cannot use some_ref for tb_records and tb_details 
relationship, as I require serial IDs of tb_records to be incremential, and 
not all tb_details rows will exist for each tb_records row:

CREATE OR REPLACE FUNCTION "public"."report_ins_upd" (in_some_text varchar, 
in_some_ref varchar) RETURNS "pg_catalog"."void" AS
$body$
BEGIN
     LOOP
     UPDATE sch_reports.tb_master SET
some_text = in_some_text,
WHERE some_ref=in_some_ref;
     IF found THEN
        RETURN;
     END IF;
     BEGIN
     INSERT INTO sch_reports.tb_master (
some_ref,
some_text
)
     VALUES (
in_some_ref,
in_some_text,
);
     RETURN;
     EXCEPTION WHEN unique_violation THEN
          -- do nothing
          END;
     END LOOP;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Thanks! 



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings