本文基于 PostgreSQL 18，完整演示时区映射表 TimeZoneMappings 的建表、数据初始化、三种分页函数（传统页码分页、游标分页、全文检索游标分页）以及 Python 调用示例。内容与 MySQL、Oracle 版本保持一致，方便多数据库迁移对照。

本节介绍运行本文示例所需的环境与依赖，包括 PostgreSQL 18 数据库、Python 3.11 以及 psycopg 驱动。

本节给出 TimeZoneMappings 表的完整建表 SQL，包括 UUID 主键、唯一约束、自动更新时间触发器以及分页所需的核心索引。

本节讲解三种分页函数的实现思路与适用场景：传统页码分页适合中小数据量，游标分页适合亿级数据，全文检索游标分页则基于 pg_trgm 扩展实现中文模糊搜索。

本节提供完整的 Python 脚本，演示如何调用上述三个分页函数，并给出运行输出示例。

本节介绍如何验证数据量与函数是否就绪，并通过 EXPLAIN  分析索引命中情况，说明不同分页方案在数据量增长时的表现差异。

总结本文要点，并给出后续在多数据库间迁移时的注意事项。

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 &lt;&gt; '' 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 &lt;&gt; '' 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" &gt; p_last_sort
              OR ("Sort" = p_last_sort AND "RowVersion" &gt; 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" &gt; p_last_sort
      OR (t."Sort" = p_last_sort AND t."RowVersion" &gt; 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 &lt;&gt; '' 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" &gt; p_last_sort
              OR ("Sort" = p_last_sort AND "RowVersion" &gt; 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" &gt; p_last_sort
      OR (t."Sort" = p_last_sort AND t."RowVersion" &gt; 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()

sql:postgreSQL 18 paging

数据库

基于PostgreSQL 18的时区映射表完整分页方案，涵盖建表、432条数据初始化及三种分页函数：传统页码分页、游标分页与pg_trgm全文检索游标分页。提供Python 3.11调用示例与EXPLAIN性能分析，支持MySQL/Oracle多数据库迁移对照。

PostgreSQL

Python

MySQL

2026上云采购 | AI焕新·智启新局

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

sql:postgreSQL 18 paging

sql:postgreSQL 18 paging

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐