视图、函数、JSONB 与分区

CREATE VIEW v_order_detail AS
SELECT o.id,
       c.name AS customer,
       b.title,
       o.quantity,
       o.amount,
       o.ordered_at
FROM   orders o
JOIN   books     b ON b.id = o.book_id
LEFT   JOIN customers c ON c.id = o.customer_id;  -- 未登录订单也保留

-- 之后像查普通表一样查询它
SELECT * FROM v_order_detail WHERE amount > 100 ORDER BY ordered_at;

CREATE VIEW v_books_in_stock AS
SELECT * FROM books WHERE stock > 0;

UPDATE v_books_in_stock SET price = price * 0.9 WHERE id = 1;  -- 实际修改 books 表

-- 加上 WITH CHECK OPTION，禁止通过视图把行改成"视图看不见"的样子
CREATE OR REPLACE VIEW v_books_in_stock AS
SELECT * FROM books WHERE stock > 0
WITH CHECK OPTION;

UPDATE v_books_in_stock SET stock = -10 WHERE id = 1;
-- 被拒绝：修改后的行不再满足 stock > 0

CREATE VIEW v_category_avg AS
SELECT category, round(avg(price), 2) AS avg_price
FROM   books
GROUP  BY category;

UPDATE v_category_avg SET avg_price = 50;
-- 报错：含聚合的视图不可自动更新，确有需要得走 INSTEAD OF 触发器（进阶话题）

CREATE MATERIALIZED VIEW mv_book_sales AS
SELECT b.id AS book_id, b.title,
       sum(o.quantity) AS total_qty,
       sum(o.amount)   AS total_revenue
FROM   orders o
JOIN   books b ON b.id = o.book_id
GROUP  BY b.id, b.title;

INSERT INTO orders (book_id, customer_id, quantity, amount)
VALUES (2, 1, 1, 129.00);                          -- 新卖出一本书

SELECT * FROM mv_book_sales;                       -- 结果没变：仍是旧快照
REFRESH MATERIALIZED VIEW mv_book_sales;           -- 手动刷新
SELECT * FROM mv_book_sales;                       -- 现在包含新订单了

CREATE UNIQUE INDEX ON mv_book_sales (book_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_book_sales;

-- 全场九折
CREATE FUNCTION discount_price(p numeric) RETURNS numeric AS $$
BEGIN
    RETURN round(p * 0.9, 2);
END;
$$ LANGUAGE plpgsql;

SELECT title, price, discount_price(price) FROM books;

CREATE FUNCTION member_price(p numeric, level text) RETURNS numeric AS $$
BEGIN
    IF level = 'gold' THEN
        RETURN round(p * 0.8, 2);   -- 金卡八折
    ELSIF level = 'silver' THEN
        RETURN round(p * 0.9, 2);   -- 银卡九折
    ELSE
        RETURN p;                   -- 普通会员原价
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT member_price(100, 'gold');   -- 80.00

CREATE FUNCTION books_in_category(cat text)
RETURNS TABLE (id bigint, title text, price numeric)
LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY
    SELECT b.id, b.title, b.price
    FROM   books b
    WHERE  b.category = cat;
END;
$$;

SELECT * FROM books_in_category('database');

CREATE PROCEDURE restock(p_book bigint, p_qty int)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE books SET stock = stock + p_qty WHERE id = p_book;
END;
$$;

CALL restock(1, 50);   -- 补 50 本《SQL 入门经典》

ALTER TABLE books ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at := now();   -- 直接修改即将写入的新行
    RETURN NEW;                -- 关键！返回 NULL 会让这一行的更新悄悄失效
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_books_updated_at
BEFORE UPDATE ON books
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- 审计表：一行记录一次变更
CREATE TABLE books_audit (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    action     text NOT NULL,                        -- 'INSERT' / 'UPDATE' / 'DELETE'
    book_id    bigint,
    old_data   jsonb,                                -- 变更前的整行
    new_data   jsonb,                                -- 变更后的整行
    changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE FUNCTION audit_books() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO books_audit (action, book_id, old_data)
        VALUES ('DELETE', OLD.id, row_to_json(OLD)::jsonb);
        RETURN OLD;
    ELSIF TG_OP = 'INSERT' THEN
        INSERT INTO books_audit (action, book_id, new_data)
        VALUES ('INSERT', NEW.id, row_to_json(NEW)::jsonb);
        RETURN NEW;
    ELSE   -- UPDATE：新旧都记，才看得出"从多少改成多少"
        INSERT INTO books_audit (action, book_id, old_data, new_data)
        VALUES ('UPDATE', OLD.id,
                row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb);
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_books_audit
AFTER INSERT OR UPDATE OR DELETE ON books
FOR EACH ROW EXECUTE FUNCTION audit_books();

INSERT INTO books (title, author, category, price, stock)
VALUES ('PostgreSQL 习题集', '赵三', 'database', 39.00, 10);

UPDATE books SET price = 35.00 WHERE title = 'PostgreSQL 习题集';

DELETE FROM books WHERE title = 'PostgreSQL 习题集';

SELECT action, book_id, old_data, new_data FROM books_audit ORDER BY id;
-- 三行记录恰好对应三次操作；UPDATE 那行同时留着旧价 39.00 与新价 35.00

CREATE TABLE events (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now(),
    data       jsonb NOT NULL
);

INSERT INTO events (data) VALUES
    ('{"event":"view_book",   "book_id":2, "tags":["postgres","beginner"]}'),
    ('{"event":"add_to_cart", "book_id":2, "tags":["postgres"], "device":"mobile"}'),
    ('{"event":"checkout",    "tags":["postgres","sale"]}');

SELECT data->'tags'->>0 AS first_tag FROM events;
-- -> 按键取子文档（结果仍是 jsonb），->> 取纯文本，可层层深入

SELECT * FROM events WHERE data @> '{"tags":["postgres"]}';
-- @> 包含判断：最常配合索引的查询写法

SELECT * FROM events WHERE data ? 'device';
-- ? 判断顶层是否存在某键（?| 任一存在、?& 全部存在，都只看顶层，不递归嵌套）

CREATE INDEX idx_events_gin ON events USING GIN (data);

CREATE INDEX idx_events_tags ON events USING GIN ((data->'tags'));
-- 专门加速 WHERE data->'tags' ? 'sale'

CREATE TABLE publishers (id int PRIMARY KEY, name text);

ALTER TABLE books ADD COLUMN publisher_id int REFERENCES publishers(id);
-- 可行：关系列上外键、CHECK 约束都能正常工作
-- 而 books.info->>'publisher' 只是 jsonb 里的一个键：对它建不了外键

CREATE TABLE payments (
    id       bigint NOT NULL,
    order_id bigint NOT NULL,
    amount   numeric(10, 2) NOT NULL,
    paid_at  timestamptz NOT NULL
) PARTITION BY RANGE (paid_at);

-- 每月一个分区；注意边界规则：下界包含、上界不包含
CREATE TABLE payments_2026_09 PARTITION OF payments
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE payments_2026_10 PARTITION OF payments
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

INSERT INTO payments VALUES (1, 1, 129.00, '2026-11-05');
-- 报错：没有任何分区能接纳 11 月的行

CREATE TABLE payments_2026_11 PARTITION OF payments
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
-- 或者：CREATE TABLE payments_default PARTITION OF payments DEFAULT;

-- 再次执行上面的 INSERT 即可成功

EXPLAIN SELECT count(*) FROM payments
WHERE  paid_at >= '2026-10-01' AND paid_at < '2026-11-01';
-- 计划里只剩 payments_2026_10（若建了 DEFAULT 分区还会带上它），
-- 9 月分区根本不会被扫描

ALTER TABLE payments DETACH PARTITION payments_2026_09;
-- 旧分区"摘下"成为独立表：可改名、导出、确认后整表删除，
-- 完全避开 DELETE + VACUUM；PG 14 起还可用 DETACH CONCURRENTLY，锁更轻

CREATE TABLE vip_customers (
    id    bigint NOT NULL,
    name  text NOT NULL,
    level text NOT NULL
) PARTITION BY LIST (level);

CREATE TABLE vip_gold   PARTITION OF vip_customers FOR VALUES IN ('gold');
CREATE TABLE vip_silver PARTITION OF vip_customers FOR VALUES IN ('silver', 'bronze');

ALTER TABLE payments ADD PRIMARY KEY (id);              -- 失败：缺分区键 paid_at
ALTER TABLE payments ADD PRIMARY KEY (id, paid_at);     -- 成功

SELECT to_tsvector('english', 'satisfy satisfies');
-- 两个词归一为同一个词素，因此搜 satisfy 能命中 satisfies

ALTER TABLE books ADD COLUMN description text NOT NULL DEFAULT '';

UPDATE books SET description =
    'A hands-on guide to Postgres indexes, replication and performance tuning.'
WHERE  title = 'PostgreSQL 实战';

UPDATE books SET description =
    'Classic theory of databases: transactions, locking and SQL optimization.'
WHERE  title = '数据库原理';

-- 表达式索引必须用带配置名的两参数形式，单参数形式依赖 default_text_search_config 设置，索引内容不可靠
CREATE INDEX books_fts_idx ON books
    USING GIN (to_tsvector('english', title || ' ' || description));

SELECT id, title FROM books
WHERE  to_tsvector('english', title || ' ' || description)
       @@ to_tsquery('postgres & index');
-- & 且、| 或、! 非、<-> 相邻；命中写着 "Postgres indexes" 的《PostgreSQL 实战》，
-- 却不会命中只谈 transactions 的《数据库原理》

SELECT id, title FROM books
WHERE  to_tsvector('english', title || ' ' || description)
       @@ phraseto_tsquery('english', 'performance tuning');
-- 短语匹配：要求 tuning 紧跟 performance，词序不能乱

-- 先看看本机有哪些扩展可用
SELECT name, default_version, installed_version
FROM   pg_available_extensions
ORDER  BY name;

CREATE EXTENSION pg_trgm;

SELECT show_trgm('word');
-- word 的全部三元组：{"  w"," wo",ord,"rd ",wor}
-- 注意切分前 pg_trgm 会先给词首补两个空格、词尾补一个空格，
-- 所以才有 "  w"、"rd " 这类带空格的三元组——补空格让词头、
-- 词尾的片段也参与比较，相似度计算才不失真

-- 顾客把《PostgreSQL 实战》的"战"打成了"占"？% 筛出相似候选，similarity() 给出相似度
SELECT title, similarity(title, 'PostgreSQL 实占') AS sim
FROM   books
WHERE  title % 'PostgreSQL 实占'
ORDER  BY sim DESC
LIMIT  3;

-- pg_trgm 的 GIN 索引还能加速 B-tree 无能为力的中缀模糊查询
CREATE INDEX idx_books_title_trgm ON books USING GIN (title gin_trgm_ops);
-- 此后 WHERE title LIKE '%实战%' 可走索引而非全表扫描