blob: cacd7a727d7d99fe9dbe1eeabcf156c45da783fc (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
-- Remove contract_id from orders (orders are tied only to directives)
ALTER TABLE orders DROP COLUMN IF EXISTS contract_id;
DROP INDEX IF EXISTS idx_orders_contract_id;
-- Add directive_name as a denormalized field for searchability
ALTER TABLE orders ADD COLUMN IF NOT EXISTS directive_name VARCHAR(500);
-- Make directive_id required for new orders (but keep existing NULLs)
-- We use a trigger approach to populate directive_name automatically
CREATE OR REPLACE FUNCTION update_order_directive_name()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.directive_id IS NOT NULL THEN
SELECT title INTO NEW.directive_name FROM directives WHERE id = NEW.directive_id;
ELSE
NEW.directive_name := NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_order_directive_name
BEFORE INSERT OR UPDATE OF directive_id ON orders
FOR EACH ROW
EXECUTE FUNCTION update_order_directive_name();
-- Backfill directive_name for existing orders
UPDATE orders o SET directive_name = d.title
FROM directives d WHERE o.directive_id = d.id;
-- Index for searching by directive_name
CREATE INDEX IF NOT EXISTS idx_orders_directive_name ON orders(directive_name);
|
