PostgreSQL存储过程中从JSON提取text数组的方法咨询

首先明确：你当前提取text数组的方式不正确。inputdata -> 'messageList'返回的是json类型的对象，PostgreSQL无法直接将其隐式转换为text[]类型，执行时会抛出类似cannot assign json to text[]的类型不匹配错误。

PostgreSQL专门的JSON转换函数 #

PostgreSQL提供了专门的函数来完成JSON数组到PostgreSQL原生数组的转换：

• 针对json类型：使用json_array_text(json)函数，它会把JSON数组中的每个字符串元素提取出来，转换成text[]类型。
• 针对jsonb类型（推荐使用，性能更好）：使用jsonb_array_text(jsonb)函数，作用和上面一致。

修正后的存储过程实现 #

你可以直接用转换函数简化逻辑，甚至不需要额外的变量：

CREATE OR REPLACE FUNCTION updateEventTable(inputdata json) 
RETURNS void AS $$
BEGIN
  -- 直接用json_array_text转换后插入
  INSERT INTO "MyTable" ("MESSAGE_LIST") 
  VALUES (json_array_text(inputdata -> 'messageList'));
END;
$$ LANGUAGE PLPGSQL;

如果你的输入参数改用jsonb类型（更推荐，查询和修改性能更优），可以这样写：

CREATE OR REPLACE FUNCTION updateEventTable(inputdata jsonb) 
RETURNS void AS $$
BEGIN
  INSERT INTO "MyTable" ("MESSAGE_LIST") 
  VALUES (jsonb_array_text(inputdata -> 'messageList'));
END;
$$ LANGUAGE PLPGSQL;

调用语句的小修正 #

因为你的函数返回void，所以调用时不需要用SELECT * from，直接执行即可：

SELECT updateEventTable('{ "messageList": ["Message1", "Message2"] }');

额外说明 #

如果JSON数组中存在非字符串类型的元素（比如数字、布尔值），json_array_text会自动将它们转换成字符串形式存入text[]数组，这在大多数场景下都是符合需求的。

内容的提问来源于stack exchange，提问作者vinod hy