aBWLbqJg

1CREATE TABLE IF NOT EXISTS sales_invoices(
2    invoice_number TEXT PRIMARY KEY,
3    billing_name TEXT
4);
5
6CREATE TABLE IF NOT EXISTS sales_invoice_lines(
7    sales_invoice_line_id TEXT PRIMARY KEY,
8    sales_invoice_fk TEXT NOT NULL,
9
10    CONSTRAINT si_lines FOREIGN KEY (sales_invoice_fk) REFERENCES sales_invoices(invoice_number) DEFERRABLE
11); 
12     
13START TRANSACTION; -- the app start transaction with
14SET CONSTRAINTS ALL DEFERRED;
15
16-- App does the batch insert for one table like
17INSERT INTO sales_invoices (invoice_number, billing_name)
18(SELECT r.invoice_number, r.billing_name
19 FROM unnest($1::sales_invoices[]) as r)
20ON CONFLICT (invoice_number)
21DO UPDATE SET billing_name = EXCLUDED.billing_name;
22
23-- And the batch insert for another table
24INSERT INTO sales_invoice_lines (sales_invoice_line_id, sales_invoice_fk)
25(SELECT r.sales_invoice_line_id, r.sales_invoice_fk
26 FROM unnest($1::sales_invoice_lines[]) as r)
27ON CONFLICT (sales_invoice_line_id)
28DO UPDATE SET sales_invoice_fk = EXCLUDED.sales_invoice_fk;
29
30-- And then select all violations like
31SELECT * FROM sales_invoice_lines sil 
32LEFT JOIN sales_invoices si ON (sil.sales_invoice_fk = si.invoice_number)
33WHERE si.invoice_number is NULL;
34
35-- If something is fetched then the app calls explicit rollback
36-- and returns the data as the report further;
37-- Commit otherwise