JSON 对象到 Postgres 存储过程参数

Question

有一个单页应用程序发送带有 JSON 负载的 POST HTTP 请求:

{"product": { "name": "product A", "quantity": 100 }}

有一个包含表和存储过程的 Postgres 数据库:

create table product {
  product_id serial primary key,
  name text,
  quantity numeric,
  description text
}

create function insert_product (product product) returns product as $$
  -- This function accepts a product type as an argument

是否有任何语言的解决方案可以位于服务器上、处理 HTTP 请求、调用存储过程并自动将 JSON 对象转换为适当的 Postgres 行类型？

在伪 Express.js 中

app.post('/product', (req, res) =>
  db.query('select insert_product($1)', [convertToPostgresPlease(req.body.product)])

我不考虑的解决方案:

• 解构 JSON 对象并用茶匙为 Postgres 提供每个键或在存储过程中处理 JSON(SP 应接受行类型)
• 从 Postgres 模式复制信息(解决方案必须使用 Postgres 内省(introspection)功能)
• 手动连接 '(' + product.name + ',' + ...

我知道存储过程经常不受欢迎，但对于小型项目，我真的认为它们很棒。 SQL 是处理数据的绝佳 DSL，而 Postgres 足够先进，可以处理任何与数据相关的任务。

无论如何，将 JSON HTTP 请求与适当的 SQL RDBMS 连接起来的最简单方法是什么？

找到的解决方案(差不多):

• postgraphql (有效，但魔法太多)
• Chicken Scheme (list-unparser，还没试过)

Answer 1

正如 Abelisto 在评论中提到的，您可以使用 json_populate_record/jsonb_populate_record 从数据库函数中的 JSON/JSONB 参数转换为特定的表行。另一种方法是直接使用 json 变量，使用 -> 和 ->> 运算符来检索其内容。这样做的缺点是需要大量代码来维护每个表。

您还可以从 RESTful 接口(interface)(例如 https://github.com/QBisConsult/psql-api)中获益。

高度基于 JSON 的解决方案的另一个选择是简化大量小表的操作，这些小表不会增长到每个记录超过几百条。会有性能损失，但对于几行，它可能可以忽略不计。

下面举例说明了 PostgreSQL 中 JSON 数据类型的强大功能以及支持 JSON 运算符的 GIN 索引。对于需要最高性能的数据，您仍然可以使用普通表和专用函数。

例子:

CREATE TABLE public.jtables (
  table_id serial NOT NULL PRIMARY KEY,
  table_name text NOT NULL UNIQUE,
  fields jsonb
);

INSERT INTO public.jtables VALUES (default, 'product', '{"id": "number", "name": "string", "quantity": "number", "description": "string"}'::jsonb);

CREATE TABLE public.jdata (
  table_id int NOT NULL REFERENCES jtables,
  data jsonb NOT NULL
);

CREATE UNIQUE INDEX ON public.jdata USING BTREE (table_id, (data->>'id'));
CREATE INDEX ON public.jdata USING GIN (data);

您可以创建函数以通用 JSON 方式操作数据，例如:

CREATE FUNCTION public.jdata_insert(_table text, _data jsonb) RETURNS text AS
$BODY$
  INSERT INTO public.jdata 
  SELECT table_id, $2
  FROM public.jtables
  WHERE table_name = $1
  RETURNING (data)->>'id';
$BODY$ LANGUAGE sql;

CREATE FUNCTION public.jdata_update(_table text, _id text, _data jsonb) RETURNS text AS
$BODY$
  UPDATE public.jdata d SET data = jsonb_strip_nulls(d.data || $3)
  FROM public.jtables t
  WHERE d.table_id = t.table_id AND t.table_name = $1 AND (d.data->>'id') = $2
  RETURNING (d.data)->>'id';
$BODY$ LANGUAGE sql;

然后可以使用这些通用函数插入行:

SELECT public.jdata_insert('product', '{"id": 1, "name": "Product 1", "quantity": 10, "description": "no description"}'::jsonb);
SELECT public.jdata_insert('product', '{"id": 2, "name": "Product 2", "quantity": 5}'::jsonb);
SELECT public.jdata_update('product', '1', '{"description": "test product"}'::jsonb);

并且可以使用现有索引以多种方式查询他们的数据:

SELECT * FROM public.jdata WHERE table_id = 1 AND (data->>'id') = '1';
SELECT * FROM public.jdata WHERE table_id = 1 AND data @> '{"quantity": 5}'::jsonb;
SELECT * FROM public.jdata WHERE table_id = 1 AND data ? 'description';

View 可以使查询更容易:

CREATE VIEW public.vjdata AS
SELECT d.table_id, t.table_name, (d.data->>'id') AS id, d.data
FROM jtables t
JOIN jdata d USING (table_id);

CREATE OR REPLACE FUNCTION public.vjdata_upsert() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    PERFORM public.jdata_insert(NEW.table_name, NEW.data);
  ELSE
    PERFORM public.jdata_update(NEW.table_name, NEW.id, NEW.data);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER vjdata_upsert_trigger INSTEAD OF INSERT OR UPDATE
  ON public.vjdata FOR EACH ROW EXECUTE PROCEDURE public.vjdata_upsert();

UPDATE public.vjdata SET
  data = data || jsonb_build_object('quantity', (data->>'quantity')::int + 2)
WHERE table_name = 'product' AND id = '2'

SELECT * FROM public.vjdata WHERE table_name = 'product' AND id = '2';