01_materialized_test_schema.sql
application/octet-stream
Filename: 01_materialized_test_schema.sql
Type: application/octet-stream
Part: 1
drop schema if exists materialized_test cascade;
create extension if not exists btree_gist;
create schema materialized_test;
create table materialized_test.buying_list (
buying_list_id uuid not null primary key default gen_random_uuid(),
buying_list_description text not null
);
--represents brands or categories of products. Simple hierarchy (each node can belong to one and only one parent node)
create table materialized_test.product_group (
product_group_id uuid not null primary key default gen_random_uuid(),
parent_product_group_id uuid null references materialized_test.product_group (product_group_id), --simple hierarchy
group_code text not null,
description text not null
);
create table materialized_test.product (
product_id uuid not null primary key default gen_random_uuid(),
sku text not null,
description text not null,
active boolean not null default true,-- soft delete
CONSTRAINT product_unique_sku UNIQUE (sku)
);
create table materialized_test.product_group_product (
product_group_id uuid not null references materialized_test.product_group(product_group_id),
product_id uuid not null references materialized_test.product(product_id),
constraint product_group_product_pkey primary key (product_group_id, product_id)
);
CREATE OR REPLACE FUNCTION materialized_test.value_exists(VARIADIC _values anyarray)
RETURNS integer AS
$BODY$
SELECT sum(CASE WHEN val IS NOT NULL THEN 1 ELSE 0 END)::int FROM unnest(_values) tmp(val);
$BODY$
LANGUAGE sql VOLATILE
COST 100;
--stores product_groups and products on the buying list
-- a descendent of a group can be specified on the same buying list as the parent group
-- a product within a group can be specified on the same buying list as that group
CREATE TABLE materialized_test.buying_list_item (
buying_list_item_id uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid() ,
buying_list_id uuid NOT NULL REFERENCES materialized_test.buying_list (buying_list_id),
product_id uuid NULL REFERENCES materialized_test.product (product_id),
product_group_id uuid NULL REFERENCES materialized_test.product_group (product_group_id),
buying_list_item_price NUMERIC,
buying_list_item_description TEXT,
buying_list_item_effective_date DATE,
buying_list_item_special BOOLEAN,
CONSTRAINT buying_list_item_one_not_null CHECK (materialized_test.value_exists(VARIADIC ARRAY[product_id, product_group_id]) = 1)
);
--"materialized" table to provide view of data to
CREATE TABLE materialized_test.buying_list_product_materialized (
buying_list_item_id uuid not null,
buying_list_id uuid not null,
product_id uuid not null,
buying_list_item_price numeric not null,
buying_list_item_description text not null,
buying_list_item_effective_date date not null,
buying_list_item_special boolean not null,
CONSTRAINT buying_list_product_materialized_pkey PRIMARY KEY (buying_list_id, product_id)
);
CREATE OR REPLACE VIEW materialized_test.product_group_hierarchy AS
WITH RECURSIVE groupings_list(original_product_group_id, parent_product_group_id, product_group_id) AS (
SELECT pg.product_group_id AS original_grouping_id,
pg.parent_product_group_id,
pg.product_group_id,
0 AS level
FROM materialized_test.product_group pg
UNION ALL
SELECT gl.original_product_group_id,
pg2.parent_product_group_id,
pg2.product_group_id,
gl.level + 1
FROM groupings_list gl
JOIN materialized_test.product_group pg2 ON pg2.parent_product_group_id = gl.product_group_id
)
SELECT groupings_list.original_product_group_id,
groupings_list.parent_product_group_id,
groupings_list.product_group_id,
groupings_list.level
FROM groupings_list;
--p_active_products_only is a client-level setting all lists for one client will either enforce this or not
CREATE OR REPLACE FUNCTION materialized_test.refresh_buying_list_product_materialized(p_buying_list_ids uuid[])
RETURNS void AS
$BODY$
BEGIN
DROP TABLE IF EXISTS t_result;
CREATE TEMPORARY TABLE t_result AS
SELECT DISTINCT ON (bli.buying_list_id, p.product_id)
bli.buying_list_item_id,
bli.buying_list_id,
p.product_id,
bli.buying_list_item_price,
bli.buying_list_item_description,
bli.buying_list_item_effective_date,
bli.buying_list_item_special
FROM materialized_test.buying_list_item bli
LEFT JOIN materialized_test.product_group_hierarchy pgh
ON pgh.original_product_group_id = bli.product_group_id
AND NOT EXISTS ( --group from this group is not already explicitly on the list
SELECT 1
FROM materialized_test.buying_list_item bli2
WHERE true
AND bli2.buying_list_id = bli.buying_list_id
AND pgh.product_group_id = bli2.product_group_id
AND bli2.product_group_id <> bli.product_group_id
)
LEFT JOIN materialized_test.product_group pg
ON pgh.product_group_id = pg.product_group_id
LEFT JOIN materialized_test.product_group_product pgp
ON pg.product_group_id = pgp.product_group_id
AND NOT EXISTS ( --product in this group is not already explicitly on the list
SELECT 1
FROM materialized_test.buying_list_item bli3
WHERE true
AND bli3.buying_list_id = bli.buying_list_id
AND pgp.product_id = bli.product_id
)
INNER JOIN materialized_test.product p
ON p.product_id = COALESCE(bli.product_id, pgp.product_id)
WHERE p.active = true
AND bli.buying_list_id IN (SELECT unnest(p_buying_list_ids))
ORDER BY bli.buying_list_id, p.product_id, pgh.level desc;
ANALYZE t_result;
DELETE
FROM materialized_test.buying_list_product_materialized blpm
WHERE blpm.buying_list_id IN (SELECT unnest(p_buying_list_ids))
AND NOT EXISTS (
SELECT 1
FROM t_result res
WHERE res = blpm
);
INSERT INTO materialized_test.buying_list_product_materialized
(
buying_list_item_id,
buying_list_id,
product_id,
buying_list_item_price,
buying_list_item_description,
buying_list_item_effective_date,
buying_list_item_special
)
SELECT buying_list_item_id,
buying_list_id,
product_id,
buying_list_item_price,
buying_list_item_description,
buying_list_item_effective_date,
buying_list_item_special
FROM t_result
ON CONFLICT DO NOTHING;
DROP TABLE IF EXISTS t_result;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION materialized_test.buying_list_item_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
THEN
SELECT array_agg(DISTINCT buying_list_id)
INTO v_buying_list_ids
FROM new_table;
END IF;
IF (TG_OP = 'DELETE')
THEN
SELECT array_agg(DISTINCT buying_list_id)
INTO v_buying_list_ids
FROM old_table;
END IF;
PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER buying_list_item_statement_trigger_d
AFTER DELETE ON materialized_test.buying_list_item
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.buying_list_item_statement_refresh_trigger();
CREATE TRIGGER buying_list_item_statement_trigger_i
AFTER INSERT ON materialized_test.buying_list_item
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.buying_list_item_statement_refresh_trigger();
CREATE TRIGGER buying_list_item_statement_trigger_u
AFTER UPDATE ON materialized_test.buying_list_item
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.buying_list_item_statement_refresh_trigger();
CREATE OR REPLACE FUNCTION materialized_test.product_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
v_product_ids uuid[];
BEGIN
IF (TG_OP = 'UPDATE')
THEN
SELECT array_agg(product_id)
INTO v_product_ids
FROM new_table;
SELECT array_agg(DISTINCT x.buying_list_id)
INTO v_buying_list_ids
FROM (
SELECT bli.buying_list_id
FROM buying_list_item bli
WHERE true
AND bli.product_id = ANY (v_product_ids)
UNION ALL
SELECT bli2.buying_list_id
FROM materialized_test.buying_list_item bli2
WHERE true
AND EXISTS (
SELECT 1
FROM materialized_test.product_group_product pgp
INNER JOIN materialized_test.product_group_hierarchy pgh
ON pgp.product_group_id = pgh.product_group_id
WHERE true
AND bli2.product_group_id = pgh.original_product_group_id
AND pgp.product_id = ANY (v_product_ids)
)
) x;
END IF;
PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--Only need to trigger on updates, newly inserted products cannot be on a buying list and to hard delete a product
-- requires deleting all of its dependencies
CREATE TRIGGER product_statement_trigger_u
AFTER UPDATE ON materialized_test.product
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.product_statement_refresh_trigger();
CREATE OR REPLACE FUNCTION materialized_test.product_group_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
THEN
SELECT array_agg(DISTINCT bli.buying_list_id)
INTO v_buying_list_ids
FROM materialized_test.buying_list_item bli
INNER JOIN materialized_test.product_group_hierarchy pgh
ON bli.product_group_id = pgh.original_product_group_id
WHERE true
AND pgh.product_group_id IN (
SELECT product_group_id
FROM new_table
);
END IF;
IF (TG_OP = 'DELETE')
THEN
SELECT array_agg(DISTINCT buying_list_id)
INTO v_buying_list_ids
FROM materialized_test.buying_list_item bli
INNER JOIN materialized_test.product_group_hierarchy pgh
ON bli.product_group_id_id = pgh.original_product_group_id_id
WHERE true
AND pgh.product_group_id IN (
SELECT product_group_id
FROM old_table
);
END IF;
PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--Only need to trigger on updates, newly inserted product_groups cannot be on a buying list and to hard delete a product
-- requires deleting all of its dependencies
CREATE TRIGGER product_group_statement_trigger_u
AFTER UPDATE ON materialized_test.product_group
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.product_group_statement_refresh_trigger();
CREATE OR REPLACE FUNCTION materialized_test.product_group_product_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE v_buying_list_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
THEN
SELECT array_agg(DISTINCT bli.buying_list_id)
INTO v_buying_list_ids
FROM materialized_test.buying_list_item bli
INNER JOIN materialized_test.product_group_hierarchy pgh
ON bli.product_group_id = pgh.original_product_group_id
WHERE true
AND EXISTS (
SELECT 1
FROM new_table
WHERE new_table.product_group_id = pgh.product_group_id
);
END IF;
IF (TG_OP = 'DELETE')
THEN
SELECT array_agg(DISTINCT bli.buying_list_id)
INTO v_buying_list_ids
FROM materialized_test.buying_list_item bli
INNER JOIN materialized_test.product_group_hierarchy pgh
ON bli.product_group_id = pgh.original_product_group_id
WHERE true
AND EXISTS (
SELECT 1
FROM old_table
WHERE old_table.product_group_id = pgh.product_group_id
);
END IF;
PERFORM materialized_test.refresh_buying_list_product_materialized(v_buying_list_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER product_group_product_statement_trigger_i
AFTER INSERT ON materialized_test.product_group_product
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.product_group_product_statement_refresh_trigger();
CREATE TRIGGER product_group_product_statement_trigger_d
AFTER DELETE ON materialized_test.product_group_product
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.product_group_product_statement_refresh_trigger();
CREATE TRIGGER product_group_product_statement_trigger_u
AFTER UPDATE ON materialized_test.product_group_product
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.product_group_product_statement_refresh_trigger();
CREATE VIEW materialized_test.check_buying_list_product_materialized
AS
SELECT DISTINCT ON (bli.buying_list_id, p.product_id)
bli.buying_list_item_id,
bli.buying_list_id,
p.product_id,
bli.buying_list_item_price,
bli.buying_list_item_description,
bli.buying_list_item_effective_date,
bli.buying_list_item_special
FROM materialized_test.buying_list_item bli
LEFT JOIN materialized_test.product_group_hierarchy pgh
ON pgh.original_product_group_id = bli.product_group_id
AND NOT EXISTS ( --group from this group is not already explicitly on the list
SELECT 1
FROM materialized_test.buying_list_item bli2
WHERE true
AND bli2.buying_list_id = bli.buying_list_id
AND pgh.product_group_id = bli2.product_group_id
AND bli2.product_group_id <> bli.product_group_id
)
LEFT JOIN materialized_test.product_group pg
ON pgh.product_group_id = pg.product_group_id
LEFT JOIN materialized_test.product_group_product pgp
ON pg.product_group_id = pgp.product_group_id
AND NOT EXISTS ( --product in this group is not already explicitly on the list
SELECT 1
FROM materialized_test.buying_list_item bli3
WHERE true
AND bli3.buying_list_id = bli.buying_list_id
AND pgp.product_id = bli.product_id
)
INNER JOIN materialized_test.product p
ON p.product_id = COALESCE(bli.product_id, pgp.product_id)
WHERE p.active = true
ORDER BY bli.buying_list_id, p.product_id, pgh.level desc;
-- Scenario 2 - denormalization of totals of price * quantity type and simple fee-type values from child
-- records for a given entity stored in two different child tables
CREATE TABLE materialized_test.fee_type (
fee_type_id uuid not null primary key default gen_random_uuid(),
fee_type_description text not null
--fee types have other attributes that aren't important in this use case
);
CREATE TABLE materialized_test.invoice (
invoice_id uuid not null primary key default gen_random_uuid(),
invoice_description text not null,
invoice_date date not null,
invoice_total numeric not null --denormalized total of all invoiced prices and invoiced fees for this invoice. users cannot submit the invoice through the application until this matches the detail
--invoices have other attributes that aren't important for this use case
);
--a invoice can have many prices for the same or different products at man different quantities. The paid price can be for some quantity of the invoiced amount and can be different than the invoiced price.
CREATE TABLE materialized_test.invoice_product_detail (
invoice_product_id uuid not null primary key default gen_random_uuid(),
invoice_id uuid not null references materialized_test.invoice(invoice_id),
product_id uuid not null references materialized_test.product(product_id),
invoiced_price numeric not null,
invoiced_quantity numeric not null,
paid_price numeric not null,
paid_quantity numeric not null
);
--an invoice can have many fees of different (or the same fee_type_id) applied
CREATE TABLE materialized_test.invoice_fee_detail (
invoice_fee_id uuid not null primary key default gen_random_uuid(),
invoice_id uuid not null references materialized_test.invoice(invoice_id),
fee_type_id uuid not null references materialized_test.fee_type(fee_type_id),
invoiced_fee numeric not null,
paid_fee numeric not null
);
--table which stores rolled up totals from both the child tables of invoice and sums of those
-- to display at the invoice level.
--shorted is the difference between invoiced and paid
CREATE TABLE materialized_test.invoice_totals_materialized (
invoice_id uuid not null primary key references materialized_test.invoice(invoice_id),
product_count numeric not null,
fee_count numeric not null,
invoiced_product_total numeric not null,
paid_product_total numeric not null,
invoiced_fee_total numeric not null,
paid_fee_total numeric not null,
invoiced_total numeric not null,
paid_total numeric not null,
balanced boolean not null default false
);
CREATE OR REPLACE FUNCTION materialized_test.invoice_statement_trigger()
RETURNS trigger AS
$BODY$
DECLARE v_invoice_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM new_table;
END IF;
IF (TG_OP = 'DELETE')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM old_table;
END IF;
PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION materialized_test.invoice_product_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE
v_invoice_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM new_table;
END IF;
IF (TG_OP = 'UPDATE')
THEN
SELECT array_agg(DISTINCT invoice_ids)
INTO v_invoice_ids
FROM (
--We unnrest the invoice_ids in the concatenated arrays to de-dup
SELECT unnest(invoice_ids) as invoice_ids
FROM (
--no need for distinct keyword here as we'll do it once above
SELECT array_agg(old_table.invoice_id) || array_agg(new_table.invoice_id) as invoice_ids
FROM new_table
INNER JOIN old_table
USING (invoice_product_id)
WHERE TRUE
AND
(
old_table.invoiced_quantity <> new_table.invoiced_quantity
OR old_table.invoiced_price <> new_table.invoiced_price
OR old_table.paid_quantity <> new_table.paid_quantity
OR old_table.paid_price <> new_table.paid_price
OR old_table.invoice_id <> new_table.invoice_id
)
) AS x
) AS y;
END IF;
IF (TG_OP = 'DELETE')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM old_table;
END IF;
PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION materialized_test.invoice_fee_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE
v_invoice_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM new_table;
END IF;
IF (TG_OP = 'UPDATE')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM (
SELECT unnest(invoice_ids) as invoice_ids
FROM
(
--no need for distinct keyword here as we'll do it once above
SELECT array_agg(old_table.invoice_id) || array_agg(new_table.invoice_id) as invoice_ids
FROM new_table
INNER JOIN old_table
USING (invoice_fee_id)
WHERE TRUE
AND (old_table.invoiced_fee <> new_table.invoiced_fee
OR old_table.paid_fee <> new_table.paid_fee
OR old_table.invoice_id <> new_table.invoice_id)
) AS x
) AS y;
END IF;
IF (TG_OP = 'DELETE')
THEN
SELECT array_agg(DISTINCT invoice_id)
INTO v_invoice_ids
FROM old_table;
END IF;
PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION materialized_test.refresh_invoice_totals_materialized(p_invoice_ids uuid[])
RETURNS void AS
$BODY$
with cte as (
SELECT invoice.invoice_id,
COALESCE(invoice_product.product_count, 0::bigint)::integer AS product_count,
COALESCE(invoice_fee.fee_count, 0::bigint)::integer AS fee_count,
ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric), 2) AS invoiced_product_total,
ROUND(COALESCE(invoice_product.paid_amount, 0::numeric), 2) AS paid_product_total,
ROUND(COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_fee_total,
ROUND(COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_fee_total,
ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_total,
ROUND(COALESCE(invoice_product.paid_amount, 0::numeric) + COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_total,
(invoice.invoice_total::double precision <-> (ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2))::double precision < 0.01::double precision) AS balanced
FROM materialized_test.invoice
LEFT JOIN (
SELECT invoice_fee_detail.invoice_id,
count(invoice_fee_detail.invoice_fee_id) AS fee_count,
sum(invoice_fee_detail.invoiced_fee) AS invoiced_amount,
sum(invoice_fee_detail.paid_fee) AS paid_amount
FROM materialized_test.invoice_fee_detail
WHERE invoice_fee_detail.invoice_id IN (SELECT unnest(p_invoice_ids))
GROUP BY invoice_fee_detail.invoice_id
) AS invoice_fee
ON invoice_fee.invoice_id = invoice.invoice_id
LEFT JOIN (
SELECT invoice_product_detail.invoice_id,
count(invoice_product_detail.invoice_product_id) as product_count,
sum(invoice_product_detail.invoiced_quantity * invoice_product_detail.invoiced_price) as invoiced_amount,
sum(invoice_product_detail.paid_quantity * invoice_product_detail.paid_price) as paid_amount
FROM materialized_test.invoice_product_detail
WHERE invoice_product_detail.invoice_id IN (SELECT unnest(p_invoice_ids))
GROUP BY invoice_product_detail.invoice_id
) AS invoice_product
ON invoice_product.invoice_id = invoice.invoice_id
WHERE invoice.invoice_id IN (SELECT unnest(p_invoice_ids))
)
INSERT INTO materialized_test.invoice_totals_materialized (
invoice_id,
product_count,
fee_count,
invoiced_product_total,
paid_product_total,
invoiced_fee_total,
paid_fee_total,
invoiced_total,
paid_total,
balanced
)
SELECT invoice_id,
product_count,
fee_count,
invoiced_product_total,
paid_product_total,
invoiced_fee_total,
paid_fee_total,
invoiced_total,
paid_total,
balanced
FROM cte
ON CONFLICT (invoice_id) DO UPDATE SET
product_count = EXCLUDED.product_count
, fee_count = EXCLUDED.fee_count
, invoiced_product_total = EXCLUDED.invoiced_product_total
, paid_product_total = EXCLUDED.paid_product_total
, invoiced_fee_total = EXCLUDED.invoiced_fee_total
, paid_fee_total = EXCLUDED.paid_fee_total
, invoiced_total = EXCLUDED.invoiced_total
, paid_total = EXCLUDED.paid_total
, balanced = EXCLUDED.balanced;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
CREATE OR REPLACE VIEW materialized_test.check_invoice_totals_materialized AS
with cte as (
SELECT invoice.invoice_id,
COALESCE(invoice_product.product_count, 0::bigint)::integer AS product_count,
COALESCE(invoice_fee.fee_count, 0::bigint)::integer AS fee_count,
ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric), 2) AS invoiced_product_total,
ROUND(COALESCE(invoice_product.paid_amount, 0::numeric), 2) AS paid_product_total,
ROUND(COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_fee_total,
ROUND(COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_fee_total,
ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2) AS invoiced_total,
ROUND(COALESCE(invoice_product.paid_amount, 0::numeric) + COALESCE(invoice_fee.paid_amount, 0::numeric), 2) AS paid_total,
(invoice.invoice_total::double precision <-> (ROUND(COALESCE(invoice_product.invoiced_amount, 0::numeric) + COALESCE(invoice_fee.invoiced_amount, 0::numeric), 2))::double precision < 0.01::double precision) AS balanced
FROM materialized_test.invoice
LEFT JOIN (
SELECT invoice_fee_detail.invoice_id,
count(invoice_fee_detail.invoice_fee_id) AS fee_count,
sum(invoice_fee_detail.invoiced_fee) AS invoiced_amount,
sum(invoice_fee_detail.paid_fee) AS paid_amount
FROM materialized_test.invoice_fee_detail
WHERE true
GROUP BY invoice_fee_detail.invoice_id
) AS invoice_fee
ON invoice_fee.invoice_id = invoice.invoice_id
LEFT JOIN (
SELECT invoice_product_detail.invoice_id,
count(invoice_product_detail.invoice_product_id) as product_count,
sum(invoice_product_detail.invoiced_quantity * invoice_product_detail.invoiced_price) as invoiced_amount,
sum(invoice_product_detail.paid_quantity * invoice_product_detail.paid_price) as paid_amount
FROM materialized_test.invoice_product_detail
WHERE true
GROUP BY invoice_product_detail.invoice_id
) AS invoice_product
ON invoice_product.invoice_id = invoice.invoice_id
WHERE true
)
SELECT invoice_id,
product_count,
fee_count,
invoiced_product_total,
paid_product_total,
invoiced_fee_total,
paid_fee_total,
invoiced_total,
paid_total,
balanced
FROM cte;
CREATE TRIGGER invoice_product_detail_statement_trigger_d
AFTER DELETE
ON materialized_test.invoice_product_detail
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_product_statement_refresh_trigger();
CREATE TRIGGER invoice_product_detail_statement_trigger_i
AFTER INSERT
ON materialized_test.invoice_product_detail
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_product_statement_refresh_trigger();
CREATE TRIGGER invoice_product_detail_statement_trigger_u
AFTER UPDATE ON materialized_test.invoice_product_detail
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_product_statement_refresh_trigger();
CREATE TRIGGER invoice_fee_detail_statement_trigger_d
AFTER DELETE
ON materialized_test.invoice_fee_detail
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_fee_statement_refresh_trigger();
CREATE TRIGGER invoice_fee_detail_statement_trigger_i
AFTER INSERT
ON materialized_test.invoice_fee_detail
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_fee_statement_refresh_trigger();
CREATE TRIGGER invoice_fee_detail_statement_trigger_u
AFTER UPDATE ON materialized_test.invoice_fee_detail
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_fee_statement_refresh_trigger();
CREATE OR REPLACE FUNCTION materialized_test.invoice_statement_refresh_trigger()
RETURNS trigger AS
$BODY$
DECLARE v_invoice_ids uuid[];
BEGIN
IF (TG_OP = 'INSERT')
THEN
SELECT array_agg(invoice_id)
INTO v_invoice_ids
FROM new_table;
END IF;
PERFORM materialized_test.refresh_invoice_totals_materialized(v_invoice_ids);
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER invoice_statement_trigger_d
AFTER DELETE ON materialized_test.invoice
REFERENCING OLD TABLE AS old_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_statement_refresh_trigger();
CREATE TRIGGER invoice_statement_trigger_i
AFTER INSERT ON materialized_test.invoice
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_statement_refresh_trigger();
CREATE TRIGGER invoice_statement_trigger_u
AFTER UPDATE ON materialized_test.invoice
REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE PROCEDURE materialized_test.invoice_statement_refresh_trigger();