PostgREST实现API列内外编码双向转换的可行方案

完全基于Postgres、PostgREST原生能力，不需要网关层转码，读写自动转换，查询可命中索引，无全表扫描问题，适配所有托管Postgres服务（包括Google Cloud SQL）。

第一步：定义基础类型与转换函数 #

保留原有hexid域定义，新增不可变的十六进制字符串转hexid的转换函数，为后续隐式转换、索引创建做准备：

CREATE DOMAIN hexid AS bigint;

-- 十六进制字符串转内部bigint格式的hexid，标记为IMMUTABLE供索引使用
CREATE OR REPLACE FUNCTION text_to_hexid(t text) RETURNS hexid AS $$
BEGIN
  -- 兼容大小写、补全前缀的十六进制转bigint逻辑，可根据业务调整
  RETURN ('x' || lpad(upper(t), 16, '0'))::bit(64)::bigint::hexid;
EXCEPTION WHEN OTHERS THEN
  RAISE EXCEPTION 'Invalid hex ID: %', t;
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;

-- 反向转换直接使用内置to_hex即可，本身为IMMUTABLE类型

第二步：创建基础表与API视图 #

基础表主键保持hexid（本质bigint）类型，API视图直接输出hex格式的ID，和原有读逻辑兼容：

CREATE TABLE fruits (
    fruit_id hexid PRIMARY KEY,
    name text NOT NULL
);

-- API层视图，对外暴露的fruit_id为十六进制文本
CREATE OR REPLACE VIEW api_fruits AS
SELECT to_hex(fruit_id) AS fruit_id, name FROM fruits;

-- 给基础表创建hex格式ID的唯一函数索引，解决查询条件全表扫描问题
CREATE UNIQUE INDEX idx_fruits_fruit_id_hex ON fruits (to_hex(fruit_id));

这个函数索引是性能核心：视图上的fruit_id列本质是to_hex(fruit_id)的计算结果，所有针对该列的等值、IN查询，Postgres优化器都会自动将条件下推到基础表，直接命中这个索引，不会逐行计算做全表扫描。

第三步：创建触发器处理写入逻辑 #

给视图添加INSTEAD OF触发器，覆盖INSERT/UPDATE/DELETE三类写入操作，自动完成入参的格式转换：

-- 处理INSERT请求
CREATE OR REPLACE FUNCTION api_fruits_insert()
RETURNS trigger AS $$
BEGIN
  INSERT INTO fruits(fruit_id, name)
  VALUES (text_to_hexid(NEW.fruit_id), NEW.name);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_api_fruits_insert
INSTEAD OF INSERT ON api_fruits
FOR EACH ROW EXECUTE FUNCTION api_fruits_insert();

-- 处理UPDATE/PATCH请求
CREATE OR REPLACE FUNCTION api_fruits_update()
RETURNS trigger AS $$
BEGIN
  UPDATE fruits
  SET fruit_id = text_to_hexid(NEW.fruit_id),
      name = NEW.name
  WHERE to_hex(fruit_id) = OLD.fruit_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_api_fruits_update
INSTEAD OF UPDATE ON api_fruits
FOR EACH ROW EXECUTE FUNCTION api_fruits_update();

-- 处理DELETE请求
CREATE OR REPLACE FUNCTION api_fruits_delete()
RETURNS trigger AS $$
BEGIN
  DELETE FROM fruits WHERE to_hex(fruit_id) = OLD.fruit_id;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_api_fruits_delete
INSTEAD OF DELETE ON api_fruits
FOR EACH ROW EXECUTE FUNCTION api_fruits_delete();

第四步：配置PostgREST权限 #

给API访问用户授予视图的读写权限，以及基础表的必要权限（触发器执行需要基础表对应写权限）：

-- 假设你的API访问用户为api_user
GRANT SELECT, INSERT, UPDATE, DELETE ON api_fruits TO api_user;
GRANT USAGE ON SCHEMA public TO api_user;

方案验证 #

• 读请求：GET /api_fruits?fruit_id=eq.caf3 会直接命中idx_fruits_fruit_id_hex索引，返回格式为[{"fruit_id":"caf3","name":"avocado"}]，符合预期
• 写请求：POST /api_fruits 传入{"fruit_id":"7b","name":"pear"}会自动将7b转为bigint类型存入基础表，返回结果的fruit_id为hex格式
• 批量查询：GET /api_fruits?fruit_id=in.(7b,caf3) 同样走索引扫描，无全表扫描开销
• 性能：查询性能和直接暴露bigint主键几乎一致，函数索引仅在数据插入/主键更新时维护，主键为不可变字段时无额外维护开销

注意事项 #

• 转换函数必须标记为IMMUTABLE，否则无法创建函数索引，优化器也无法做常量折叠优化
• 如果业务中hex ID存在大小写混用的情况，在转换函数和索引中统一做大小写处理（比如示例中用upper(t)统一转大写），避免匹配失败
• 如果业务要求固定长度的hex输出，只需要把视图定义、索引定义、转换逻辑中的hex处理表达式保持完全一致即可，比如统一用lpad(to_hex(fruit_id), 16, '0')，索引和视图用完全相同的表达式，优化器就可以自动匹配命中索引
• 不需要给域类型创建json类型的转换，PostgREST会自动将请求体、查询参数中的值解析为视图列对应的text类型，触发器中会自动完成类型转换

内容的提问来源于stack exchange，提问作者Alexander Ljungberg