53 lines
1.2 KiB
PL/PgSQL
53 lines
1.2 KiB
PL/PgSQL
CREATE TABLE IF NOT EXISTS meeting_number_counter (
|
|
period_ym CHAR(7) PRIMARY KEY,
|
|
last_value INTEGER NOT NULL DEFAULT 0,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION next_meeting_number(p_date DATE DEFAULT CURRENT_DATE)
|
|
RETURNS TEXT
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
v_period TEXT;
|
|
v_seq INTEGER;
|
|
BEGIN
|
|
v_period := TO_CHAR(p_date, 'YYYY_MM');
|
|
|
|
LOOP
|
|
UPDATE meeting_number_counter
|
|
SET last_value = last_value + 1,
|
|
updated_at = NOW()
|
|
WHERE period_ym = v_period
|
|
RETURNING last_value INTO v_seq;
|
|
|
|
IF FOUND THEN
|
|
EXIT;
|
|
END IF;
|
|
|
|
BEGIN
|
|
INSERT INTO meeting_number_counter(period_ym, last_value)
|
|
VALUES (v_period, 1);
|
|
v_seq := 1;
|
|
EXIT;
|
|
EXCEPTION
|
|
WHEN unique_violation THEN
|
|
-- paralleler Insert, erneut versuchen
|
|
END;
|
|
END LOOP;
|
|
|
|
RETURN 'SITZ-' || v_period || '_' || LPAD(v_seq::TEXT, 4, '0');
|
|
END;
|
|
$$;
|
|
|
|
UPDATE meeting
|
|
SET meeting_number = next_meeting_number(COALESCE(meeting_date, CURRENT_DATE))
|
|
WHERE meeting_number IS NULL
|
|
OR TRIM(meeting_number) = '';
|
|
|
|
ALTER TABLE meeting
|
|
ALTER COLUMN meeting_number SET DEFAULT next_meeting_number();
|
|
|
|
ALTER TABLE meeting
|
|
ALTER COLUMN meeting_number SET NOT NULL;
|