39 lines
1.1 KiB
PL/PgSQL
39 lines
1.1 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS decision_number_counter (
|
|
period_key VARCHAR(7) PRIMARY KEY,
|
|
last_value INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION next_decision_number(p_now TIMESTAMPTZ DEFAULT NOW())
|
|
RETURNS VARCHAR(50)
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_period_key VARCHAR(7);
|
|
v_next_value INTEGER;
|
|
BEGIN
|
|
v_period_key := TO_CHAR(p_now, 'YYYY_MM');
|
|
|
|
INSERT INTO decision_number_counter (period_key, last_value, updated_at)
|
|
VALUES (v_period_key, 1, NOW())
|
|
ON CONFLICT (period_key)
|
|
DO UPDATE SET
|
|
last_value = decision_number_counter.last_value + 1,
|
|
updated_at = NOW()
|
|
RETURNING decision_number_counter.last_value INTO v_next_value;
|
|
|
|
RETURN 'BES-' || v_period_key || '_' || LPAD(v_next_value::TEXT, 4, '0');
|
|
END;
|
|
$$;
|
|
|
|
UPDATE decision
|
|
SET decision_number = next_decision_number(created_at)
|
|
WHERE decision_number IS NULL
|
|
OR TRIM(decision_number) = '';
|
|
|
|
ALTER TABLE decision
|
|
ALTER COLUMN decision_number SET DEFAULT next_decision_number();
|
|
|
|
ALTER TABLE decision
|
|
ALTER COLUMN decision_number SET NOT NULL;
|