带有变量的 JSON 对象到 PostgreSQL

Question

我有以下功能:

create or replace function something(id integer)
  RETURNS INTEGER
language plpgsql
as $$
  DECLARE
  name varchar;
  email varchar;
  route varchar;
  now timestamp;
  BEGIN
    now = current_timestamp;
    SELECT apd.apd_nome, apd.apd_email, CAST(r.rota_params->>'url_completa' as varchar) into name, email, route FROM avise_produto_disponivel apd
      inner join produto p on apd.pro_id = p.pro_id
      inner join produto_div div on p.pro_id = div.pro_id
      inner join produto_oferta_div pod on div.pod_id = pod.pod_id
      inner join produto_oferta po on po.ofe_id = pod.ofe_id
      inner join rotas r on CAST(r.rota_params->>'oferta_id' as integer) = po.ofe_id
    where apd.pro_id = $1;
    DELETE FROM avise_produto_disponivel where pro_id = $1;
    rota = CONCAT('https://www.something.com/', route);
    INSERT INTO comunicacao (cot_id, com_para, com_assunto, com_data_criado, com_parametros, com_para_nome, cos_id, com_de) VALUES (1, email, 'Produto Chegou', now, '{"NOME": name, "URL": route}', name, 1, 'correio');
    return 1;
  END;
$$ ;

但是当我尝试执行时出现以下错误:

[22P02] ERROR: invalid input syntax for type json Detalhe: Token "nome" is invalid. Where: JSON data, line 1: {"NOME": name... PL/pgSQL function avise_me(integer) line 18 at SQL statement

提前致谢。

Answer 1

可以通过castin,test来测试JSON语法的使用情况

   SELECT '{"NOME": "JOÃO", "URL": "http://rota.com.br"}'::json;

它是一个转换为 JSON 的字符串表示形式……而且不是您需要的。尝试在 INSERT INTO comunicacao 处使用

  json_build_object('nome', name,  'URL', route)

参见 https://www.postgresql.org/docs/current/functions-json.html

---

PS1: 似乎在最初的“SELECT INTO”处也有错误。

PS2:我更喜欢在几乎所有应用程序中使用 JSONb 而不是 JSON。