将json条目转换为表记录的最佳方法

debugcn 发表于 Dev

走开

我有一张桌子：

CREATE TABLE IF NOT EXISTS "account" (
  "id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "nickname" TEXT NOT NULL UNIQUE,
  "time_updated" TIMESTAMPTZ NOT NULL
);

和一个功能：

CREATE OR REPLACE FUNCTION account_register(
  e JSON,
  t TIMESTAMPTZ DEFAULT now()
) RETURNS JSON AS $$
DECLARE
  o_account ACCOUNT;
BEGIN
  INSERT INTO "account" (nickname, time_updated) VALUES (e -> 'nickname', t) RETURNING * INTO o_account;
  RETURN row_to_json(o_account);
END
$$ LANGUAGE 'plpgsql';

它像

SELECT account_register('{"nickname":"bob"}'); 

--
-- {"id"":"d3a771a9-fcff-419f-ad80-b8f5caa847da",
--  "nickname":"bob",
--  "time_updated":"2021-02-08T00:32:25.698293"}
--

我希望能够替换e JSONtoacc ACCOUNT并使它看起来像这样：

CREATE OR REPLACE FUNCTION account_register_new(
  acc ACCOUNT, -- was JSON
  t TIMESTAMPTZ DEFAULT now()
) RETURNS ACCOUNT AS $$ -- was JSON
DECLARE
  o_account ACCOUNT;
BEGIN
  -- <2. NEEDS HELP>
  INSERT INTO "account" <RECORD acc> RETURNING * INTO o_account;
  RETURN o_account
END
$$ LANGUAGE 'plpgsql';

使用方式如下：

-- <1. NEEDS HELP>
SELECT account_register(<TO_ACCOUNT>('{"nickname":"bob"}'));

所以我有两个问题：

如何将json{"nickname":"bob"}转换为记录type ACCOUNT
如何将记录type ACCOUNT插入ACCOUNT表

a_horse_with_no_name

您需要从函数中传递的记录中访问列：

CREATE OR REPLACE FUNCTION account_register(
  p_new_row account, 
  p_time_updated TIMESTAMPTZ DEFAULT now()
) 
  RETURNS account
AS $$
  insert into account (nickname, time_updated)
  values ((p_new_row).nickname, p_time_updated)
  returning *;
$$ 
LANGUAGE sql;

要将JSON值转换为已知记录类型，请使用 json_populate_record()

select account_register(json_populate_record (null::account, '{"nickname":"bob"}'));

顺便说一句：您可以接受JSON值的函数可以简化。无需使用PL / pgSQL。并且您应该使用提取昵称，->>以便获得一个text值，而不是json（或jsonb）。

CREATE OR REPLACE FUNCTION account_register(
  p_new_row jsonb, 
  p_time_updated  TIMESTAMPTZ DEFAULT now()
) 
  RETURNS jsonb 
AS $$
  with new_account as (
    INSERT INTO "account" (nickname, time_updated) 
    VALUES (e ->> 'nickname', t) 
    RETURNING *
  )
  select to_jsonb(new_account)
  from new_account;
$$ 
LANGUAGE sql;

jsonb 是建议在Postgres中使用JSON的数据类型。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。