sql:postgreSQL 18 paging

 

CREATE DATABASE "geovindu"
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Chinese (Simplified)_China.936'
    LC_CTYPE = 'Chinese (Simplified)_China.936'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

COMMENT ON DATABASE "geovindu"  --進入資料庫
    IS 'geovindu';


-- ============================================================
-- PostgreSQL 18: TimeZoneMappings 建表 + 432 条数据 + 分页函数
-- 共 432 条唯一 IANA 时区, Sort 1..432（与 MySQL/Oracle 版数据完全一致）
-- 执行工具: psql / DBeaver / PgAdmin 均可
-- ============================================================

-- 0) 清理旧表（首次执行可注释掉）
DROP TABLE IF EXISTS TimeZoneMappings CASCADE;

-- 1) 建表（双引号保留驼峰列名，与 MySQL/Oracle 版一致；
--    TimeZoneId 用 UUID + gen_random_uuid()（PG13+ 内置，无需扩展）
CREATE TABLE "TimeZoneMappings" (
    "TimeZoneId"        UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
    "IanaTimeZoneId"    VARCHAR(100),
    "WindowsTimeZoneId" VARCHAR(100)  NOT NULL,
    "DailyResetOffset"  INTEGER       NOT NULL,
    "Sort"              INTEGER       NOT NULL DEFAULT 0,
    "DisplayName"       VARCHAR(200),
    "DisplayNameHK"     VARCHAR(200),
    "DisplayNameEN"     VARCHAR(200),
    "IsActive"          SMALLINT      NOT NULL DEFAULT 1,
    "CreatedAt"         TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "UpdatedAt"         TIMESTAMP,
    "RowVersion"        BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL UNIQUE,
    CONSTRAINT "UQ_IanaTimeZoneId" UNIQUE ("IanaTimeZoneId")
);

-- 2) UpdatedAt 自动更新触发器（PostgreSQL 无 ON UPDATE）
CREATE OR REPLACE FUNCTION trg_tz_set_updated() RETURNS TRIGGER AS $$
BEGIN
    NEW."UpdatedAt" := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_tz_updated
BEFORE UPDATE ON "TimeZoneMappings"
FOR EACH ROW EXECUTE FUNCTION trg_tz_set_updated();

-- 3) 游标分页核心索引
CREATE INDEX "IX_IsActive_Sort" ON "TimeZoneMappings" ("IsActive", "Sort", "RowVersion");




-- 5) 页码分页函数（每行带 total_count，Python 取第一行即可）
CREATE OR REPLACE FUNCTION sp_timezone_page(
    p_keyword     TEXT    DEFAULT NULL,
    p_page_number INTEGER DEFAULT 1,
    p_page_size   INTEGER DEFAULT 20
) RETURNS TABLE (
    "total_count"       BIGINT,
    "TimeZoneId"        UUID,
    "IanaTimeZoneId"    TEXT,
    "WindowsTimeZoneId" TEXT,
    "DailyResetOffset"  INTEGER,
    "Sort"              INTEGER,
    "RowVersion"        BIGINT,
    "DisplayName"       TEXT,
    "DisplayNameHK"     TEXT,
    "DisplayNameEN"     TEXT
) LANGUAGE plpgsql AS $$
DECLARE
    v_kw TEXT := '%';
    v_offset INTEGER;
BEGIN
    IF p_page_number IS NULL OR p_page_number < 1 THEN p_page_number := 1; END IF;
    IF p_page_size IS NULL OR p_page_size < 1 THEN p_page_size := 20;
    ELSIF p_page_size > 200 THEN p_page_size := 200;
    END IF;
    v_offset := (p_page_number - 1) * p_page_size;

    IF p_keyword IS NOT NULL AND p_keyword <> '' THEN
        v_kw := '%' || replace(replace(replace(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%';
    END IF;

    RETURN QUERY
    SELECT
        (SELECT COUNT(*)::BIGINT FROM "TimeZoneMappings"
         WHERE "IsActive" = 1
           AND (v_kw = '%' OR "DisplayName" LIKE v_kw ESCAPE '\'
                         OR "DisplayNameHK" LIKE v_kw ESCAPE '\'
                         OR "DisplayNameEN" LIKE v_kw ESCAPE '\')),
        t."TimeZoneId", t."IanaTimeZoneId", t."WindowsTimeZoneId", t."DailyResetOffset",
        t."Sort", t."RowVersion", t."DisplayName", t."DisplayNameHK", t."DisplayNameEN"
    FROM "TimeZoneMappings" t
    WHERE t."IsActive" = 1
      AND (v_kw = '%' OR t."DisplayName" LIKE v_kw ESCAPE '\'
                    OR t."DisplayNameHK" LIKE v_kw ESCAPE '\'
                    OR t."DisplayNameEN" LIKE v_kw ESCAPE '\')
    ORDER BY t."Sort", t."RowVersion"
    LIMIT p_page_size OFFSET v_offset;
END;
$$;

-- 6) 游标分页函数（亿级主推；每行带 has_more，与 MySQL/Oracle 版同语义）
CREATE OR REPLACE FUNCTION sp_timezone_cursor(
    p_keyword          TEXT   DEFAULT NULL,
    p_last_sort        INTEGER DEFAULT NULL,
    p_last_row_version BIGINT DEFAULT NULL,
    p_page_size        INTEGER DEFAULT 20
) RETURNS TABLE (
    "has_more"          BOOLEAN,
    "TimeZoneId"        UUID,
    "IanaTimeZoneId"    TEXT,
    "WindowsTimeZoneId" TEXT,
    "DailyResetOffset"  INTEGER,
    "Sort"              INTEGER,
    "RowVersion"        BIGINT,
    "DisplayName"       TEXT,
    "DisplayNameHK"     TEXT,
    "DisplayNameEN"     TEXT
) LANGUAGE plpgsql AS $$
DECLARE
    v_first_page BOOLEAN;
    v_kw TEXT := '%';
BEGIN
    IF p_page_size IS NULL OR p_page_size < 1 THEN p_page_size := 20;
    ELSIF p_page_size > 200 THEN p_page_size := 200;
    END IF;
    v_first_page := (p_last_sort IS NULL OR p_last_row_version IS NULL);

    IF p_keyword IS NOT NULL AND p_keyword <> '' THEN
        v_kw := '%' || replace(replace(replace(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%';
    END IF;

    RETURN QUERY
    SELECT
        EXISTS (
            SELECT 1 FROM "TimeZoneMappings"
            WHERE "IsActive" = 1
              AND (v_kw = '%' OR "DisplayName" LIKE v_kw ESCAPE '\'
                            OR "DisplayNameHK" LIKE v_kw ESCAPE '\'
                            OR "DisplayNameEN" LIKE v_kw ESCAPE '\')
              AND (v_first_page OR ("Sort" > p_last_sort
                  OR ("Sort" = p_last_sort AND "RowVersion" > p_last_row_version)))),
        t."TimeZoneId", t."IanaTimeZoneId", t."WindowsTimeZoneId", t."DailyResetOffset",
        t."Sort", t."RowVersion", t."DisplayName", t."DisplayNameHK", t."DisplayNameEN"
    FROM "TimeZoneMappings" t
    WHERE t."IsActive" = 1
      AND (v_kw = '%' OR t."DisplayName" LIKE v_kw ESCAPE '\'
                    OR t."DisplayNameHK" LIKE v_kw ESCAPE '\'
                    OR t."DisplayNameEN" LIKE v_kw ESCAPE '\')
      AND (v_first_page OR (t."Sort" > p_last_sort
          OR (t."Sort" = p_last_sort AND t."RowVersion" > p_last_row_version)))
    ORDER BY t."Sort", t."RowVersion"
    LIMIT p_page_size;
END;
$$;

-- 7) 全文检索游标分页函数（对应 MySQL/Oracle 的 sp_timezone_search_ft）
--     中文检索索引: pg_trgm（PostgreSQL 官方 contrib 扩展, 3-gram, 无需编译第三方插件）
--     如需中文 2-gram 可改用 pg_bigm 扩展（第三方），函数本体不变

SELECT name, installed_version, available_version
FROM pg_available_extensions
WHERE name = 'pg_trgm';

-- 确认索引（应看到 2 个：IX_IsActive_Sort、ft_idx_display_trgm）
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'TimeZoneMappings';


CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 三列拼接后的 trgm GIN 索引（表达式须与函数内查询完全一致才能命中索引）
CREATE INDEX "ft_idx_display_trgm"
ON "TimeZoneMappings"
USING GIN (("DisplayName" || ' ' || "DisplayNameHK" || ' ' || "DisplayNameEN") gin_trgm_ops);


CREATE OR REPLACE FUNCTION sp_timezone_search_ft(
    p_keyword          TEXT   DEFAULT NULL,
    p_last_sort        INTEGER DEFAULT NULL,
    p_last_row_version BIGINT DEFAULT NULL,
    p_page_size        INTEGER DEFAULT 20
) RETURNS TABLE (
    "has_more"          BOOLEAN,
    "TimeZoneId"        UUID,
    "IanaTimeZoneId"    TEXT,
    "WindowsTimeZoneId" TEXT,
    "DailyResetOffset"  INTEGER,
    "Sort"              INTEGER,
    "RowVersion"        BIGINT,
    "DisplayName"       TEXT,
    "DisplayNameHK"     TEXT,
    "DisplayNameEN"     TEXT
) LANGUAGE plpgsql AS $$
DECLARE
    v_first_page BOOLEAN;
    v_kw TEXT := '%';
BEGIN
    IF p_page_size IS NULL OR p_page_size < 1 THEN p_page_size := 20;
    ELSIF p_page_size > 200 THEN p_page_size := 200;
    END IF;
    v_first_page := (p_last_sort IS NULL OR p_last_row_version IS NULL);

    IF p_keyword IS NOT NULL AND p_keyword <> '' THEN
        v_kw := '%' || replace(replace(replace(p_keyword, '\', '\\'), '%', '\%'), '_', '\_') || '%';
    END IF;

    RETURN QUERY
    SELECT
        EXISTS (
            SELECT 1 FROM "TimeZoneMappings"
            WHERE "IsActive" = 1
              AND (v_kw = '%' OR ("DisplayName" || ' ' || "DisplayNameHK" || ' ' || "DisplayNameEN") LIKE v_kw ESCAPE '\')
              AND (v_first_page OR ("Sort" > p_last_sort
                  OR ("Sort" = p_last_sort AND "RowVersion" > p_last_row_version)))),
        t."TimeZoneId", t."IanaTimeZoneId", t."WindowsTimeZoneId", t."DailyResetOffset",
        t."Sort", t."RowVersion", t."DisplayName", t."DisplayNameHK", t."DisplayNameEN"
    FROM "TimeZoneMappings" t
    WHERE t."IsActive" = 1
      AND (v_kw = '%' OR (t."DisplayName" || ' ' || t."DisplayNameHK" || ' ' || t."DisplayNameEN") LIKE v_kw ESCAPE '\')
      AND (v_first_page OR (t."Sort" > p_last_sort
          OR (t."Sort" = p_last_sort AND t."RowVersion" > p_last_row_version)))
    ORDER BY t."Sort", t."RowVersion"
    LIMIT p_page_size;
END;
$$;


-- 1) 数据量
SELECT COUNT(*) AS cnt, COUNT(DISTINCT "IanaTimeZoneId") AS uniq FROM "TimeZoneMappings";  -- 应为 432 / 432

-- 2) 三个函数是否齐
SELECT proname FROM pg_proc
WHERE proname IN ('sp_timezone_page','sp_timezone_cursor','sp_timezone_search_ft')
ORDER BY proname;  -- 应返回 3 行

EXPLAIN (ANALYZE, BUFFERS)
SELECT "Sort", "RowVersion", "DisplayName"
FROM "TimeZoneMappings"
WHERE "IsActive" = 1
  AND ("DisplayName" || ' ' || "DisplayNameHK" || ' ' || "DisplayNameEN") LIKE '%岛%'
ORDER BY "Sort", "RowVersion"
LIMIT 20;

-- ============================================================
-- 验证: SELECT COUNT(*) FROM "TimeZoneMappings";  -- 应返回 432
--       SELECT COUNT(DISTINCT "IanaTimeZoneId") FROM "TimeZoneMappings";  -- 432
-- ============================================================

　　

# encoding: utf-8 
# 版权所有  2026 ©涂聚文有限公司™ ®
# 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎
# 描述：postgreSQL 18.0
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2024.3.6 python 3.11
# os        : windows 10
# database  : mysql 9.0 sql server 2019, postgreSQL 18.0  Oracle 21c Neo4j
# Datetime  : 2026/9/12 11:42 
# User      :  geovindu
# Product   : PyCharm
# Project   : Pysimple
# File      : postsqlgrestlpaging.py
# -*- coding: utf-8 -*-
"""
PostgreSQL 18 版 TimeZoneMappings 分页调用示例（对应 MySQL/Oracle 版）
依赖: pip install "psycopg[binary]"
用法: 先执行 timezone_postgresql_18.sql 建表+插数+建函数, 再改下方 PG 连接参数运行本脚本
sp_timezone_page
sp_timezone_cursor
sp_timezone_search_ft
"""
import psycopg

# ============ 连接参数（改成你自己的） ============
PG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "postgres",
    "user": "postgres",
    "password": "geovindu",
}
# ================================================

conn = psycopg.connect(**PG)


def page(keyword=None, page_number=1, page_size=20):
    """传统页码分页: 返回 (rows, total_count)"""
    with conn.cursor() as cur:
        rows = cur.execute(
            "SELECT * FROM sp_timezone_page(%s, %s, %s)",
            (keyword, page_number, page_size),
        ).fetchall()
    total = rows[0][0] if rows else 0
    return rows, total


def cursor_page(keyword=None, last_sort=None, last_row_version=None, page_size=20):
    """游标分页(亿级主推): 传上次页尾 (Sort, RowVersion), 首页传 None。
       返回 (rows, has_more)"""
    with conn.cursor() as cur:
        rows = cur.execute(
            "SELECT * FROM sp_timezone_cursor(%s, %s, %s, %s)",
            (keyword, last_sort, last_row_version, page_size),
        ).fetchall()
    has_more = bool(rows[0][0]) if rows else False
    return rows, has_more


def search_ft(keyword=None, last_sort=None, last_row_version=None, page_size=20):
    """全文检索游标分页(pg_trgm, 对应 sp_timezone_search_ft):
       匹配三列拼接后的全文, 同样基于 (Sort, RowVersion) 游标。
       返回 (rows, has_more)"""
    with conn.cursor() as cur:
        rows = cur.execute(
            "SELECT * FROM sp_timezone_search_ft(%s, %s, %s, %s)",
            (keyword, last_sort, last_row_version, page_size),
        ).fetchall()
    has_more = bool(rows[0][0]) if rows else False
    return rows, has_more


def print_rows(rows, first_col_is_meta=True):
    # 列: [meta] TimeZoneId, IanaTimeZoneId, WindowsTimeZoneId, DailyResetOffset,
    #      Sort, RowVersion, DisplayName, DisplayNameHK, DisplayNameEN
    off = 1 if first_col_is_meta else 0
    for r in rows:
        print(f"  sort={r[off + 4]:>3} rv={r[off + 5]:>4} {r[off + 1]:<28} {r[off + 6]}")


def demo_page():
    print("=" * 70)
    print("[1] 传统页码分页: 第 2 页, 每页 5 条, 关键词='岛'")
    print("=" * 70)
    rows, total = page(keyword="岛", page_number=2, page_size=5)
    print(f"总命中: {total} 条")
    print_rows(rows)


def demo_cursor():
    print("=" * 70)
    print("[2] 游标分页(亿级): 关键词='北', 每页 3 条, 从头翻到尾")
    print("=" * 70)
    keyword = "北"
    last_sort, last_rv = None, None
    page_no = 1
    while True:
        rows, has_more = cursor_page(keyword, last_sort, last_rv, 3)
        if not rows:
            break
        print(f"-- 第 {page_no} 页 --")
        print_rows(rows)
        last_sort, last_rv = rows[-1][5], rows[-1][6]  # Sort, RowVersion
        if not has_more:
            break
        page_no += 1
    print(f"翻页结束, 共 {page_no} 页")


def demo_all():
    print("=" * 70)
    print("[3] 游标分页: 无关键词, 每页 20 条, 从头翻到尾")
    print("=" * 70)
    last_sort, last_rv = None, None
    page_no = 1
    total_rows = 0
    while True:
        rows, has_more = cursor_page(None, last_sort, last_rv, 20)
        if not rows:
            break
        total_rows += len(rows)
        print(f"第 {page_no} 页: {len(rows)} 条, 尾游标 sort={rows[-1][5]} rv={rows[-1][6]}, has_more={has_more}")
        last_sort, last_rv = rows[-1][5], rows[-1][6]
        if not has_more:
            break
        page_no += 1
    print(f"全部翻完: {total_rows} 条 / {page_no} 页 (应为 432 条)")


def demo_ft():
    print("=" * 70)
    print("[4] 全文检索游标分页(pg_trgm): 关键词='北', 每页 5 条")
    print("=" * 70)
    keyword = "北"
    last_sort, last_rv = None, None
    page_no = 1
    total_rows = 0
    while True:
        rows, has_more = search_ft(keyword, last_sort, last_rv, 5)
        if not rows:
            break
        print(f"-- 第 {page_no} 页 (has_more={has_more}) --")
        print_rows(rows)
        total_rows += len(rows)
        last_sort, last_rv = rows[-1][5], rows[-1][6]
        if not has_more:
            break
        page_no += 1
    print(f"全文检索共 {total_rows} 条 / {page_no} 页")


if __name__ == "__main__":
    try:
        demo_page()
        demo_cursor()
        demo_all()
        demo_ft()
    finally:
        conn.close()

　　

输出：