Add columns to acq.purchase_order: order_date and name.
Name defaults to the id, as text.
Name should be unique for a given ordering_agency and order
date (truncated to midnight), but only where order_date
is not null.
To change an existing table, run the following
through psql:
-- Add new columns; populate name
ALTER TABLE acq.purchase_order
ADD COLUMN order_date TIMESTAMP WITH TIME ZONE;
ALTER TABLE acq.purchase_order
ADD COLUMN name TEXT;
UPDATE acq.purchase_order
SET name = id::TEXT;
ALTER TABLE acq.purchase_order
ALTER COLUMN name SET NOT NULL;
-- Name should default to the id. We can't do that with a DEFAULT
-- clause but we can do it with a trigger.
CREATE OR REPLACE FUNCTION acq.purchase_order_name_default () RETURNS TRIGGER
AS $$
BEGIN
IF NEW.name IS NULL THEN
NEW.name := NEW.id::TEXT;
END IF;
RETURN NEW;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER po_name_default_trg
BEFORE INSERT OR UPDATE ON acq.purchase_order
FOR EACH ROW EXECUTE PROCEDURE acq.purchase_order_name_default ();
-- Name should be unique for a given ordering_agency and day, where
-- order_date is not null. We can't do that with a check constraint
-- because it would require a subquery, so we use a trigger.
CREATE INDEX acq_po_org_name_order_date_idx
ON acq.purchase_order( ordering_agency, name, order_date );
CREATE OR REPLACE FUNCTION acq.po_org_name_date_unique () RETURNS TRIGGER
AS $$
DECLARE
collision INT;
BEGIN
--
-- If order_date is not null, then make sure we don't have a collision
-- on order_date (truncated to day), org, and name
--
IF NEW.order_date IS NULL THEN
RETURN NEW;
END IF;
--
-- In the WHERE clause, we compare the order_dates without regard to time of day.
-- We use a pair of inequalities instead of comparing truncated dates so that the
-- query can do an indexed range scan.
--
SELECT 1 INTO collision
FROM acq.purchase_order
WHERE
ordering_agency = NEW.ordering_agency
AND name = NEW.name
AND order_date >= date_trunc( 'day', NEW.order_date )
AND order_date < date_trunc( 'day', NEW.order_date ) + '1 day'::INTERVAL
AND id <> NEW.id;
--
IF collision IS NULL THEN
-- okay, no collision
RETURN NEW;
ELSE
-- collision; nip it in the bud
RAISE EXCEPTION 'Colliding purchase orders: ordering_agency %, date %, name ''%''',
NEW.ordering_agency, NEW.order_date, NEW.name;
END IF;
END;
$$ LANGUAGE PLPGSQL;
CREATE TRIGGER po_org_name_date_unique_trg
BEFORE INSERT OR UPDATE ON acq.purchase_order
FOR EACH ROW EXECUTE PROCEDURE acq.po_org_name_date_unique ();
git-svn-id: svn://svn.open-ils.org/ILS/trunk@13924
dcc99617-32d9-48b4-a31d-
7c20da2025e4