-- 范圍分區(qū)
CREATE TABLE orders (
    order_id serial,
    customer_id int NOT NULL,
    order_status char(1) NOT NULL CHECK (order_status IN ('P', 'S', 'C')),
    order_date date NOT NULL
) PARTITION BY RANGE (order_date);

-- 列表分區(qū)
CREATE TABLE orders (
    order_id serial,
    customer_id int NOT NULL,
    order_status char(1) NOT NULL CHECK (order_status IN ('P', 'S', 'C')),
    order_date date NOT NULL
) PARTITION BY LIST (order_status );

-- 范圍分區(qū)
-- 2023年訂單
CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 2024年訂單
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 列表分區(qū)
CREATE TABLE orders_pending PARTITION OF orders
    FOR VALUES IN ('P');

CREATE TABLE orders_shipped PARTITION OF orders
    FOR VALUES IN ('S');

CREATE TABLE orders_completed PARTITION OF orders
    FOR VALUES IN ('C');

insert into orders(customer_id,order_status,order_date) 
values(1,'P',date '2023-12-01'),(1,'S',date '2024-12-01'),(2,'S',date '2023-10-01');

CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    customer_id int NOT NULL,
    order_status char(1) NOT NULL CHECK (order_status IN ('P', 'S', 'C')),
    order_date date NOT NULL
) ;

CREATE TABLE orders_2023 (
    CHECK (order_date >= '2023-01-01' AND order_date < '2024-01-01')
) INHERITS (orders);

CREATE TABLE orders_2024 (
    CHECK (order_date >= '2024-01-01' AND order_date < '2025-01-01')
) INHERITS (orders);

CREATE INDEX orders_2023_order_date ON orders_2023(order_date);
CREATE INDEX orders_2024_order_date ON orders_2024(order_date);

CREATE OR REPLACE FUNCTION orders_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
  IF(NEW.order_date >= '2023-01-01' AND NEW.order_date < '2024-01-01') THEN
    INSERT INTO orders_2023 VALUES(NEW.*);
  ELSEIF(NEW.order_date >= '2024-01-01' AND NEW.order_date < '2025-01-01') THEN
    INSERT INTO orders_2024 VALUES(NEW.*);
  ELSE
    RAISE EXCEPTION 'Date out of range. Fix the orders_insert_trigger() function!';
  END IF;
  RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_order_trigger BEFORE INSERT ON orders FOR EACH ROW EXECUTE PROCEDURE orders_insert_trigger();

DROP TABLE orders_2023;

ALTER TABLE orders DETACH PARTITION orders_2023;