PostgreSQL函数ON INSERT，将JSON值插入到另一个表中

Question

我正试图在PostgreSQL中编写一个函数，通过监听Cloudant，在插入Cloudant文档时接收一条记录(我想让它成为lambda，但现在它是一个节点进程(。

我获取Cloudant id和完整的文档，并将其插入到具有列的case_raw表中：char(32(id和json类型raw_json。

CREATE TABLE report_db.case_raw
(
doc_id character(32) NOT NULL PRIMARY KEY,
raw_json jsonb NOT NULL
)

从Cloudant到Postgres的努力是为了让我可以对来自JSON形式的边缘设备的数据进行复杂的连接等

这里是Cloudant文档的一个示例，我在id列中使用相同的id，它将插入到raw_json中。

{
"name": {
"id": "4e1e3425ef49b3b028683a5cb06ca585",
"doc": {
"_id": "4e1e3425ef49b3b028683a5cb06ca585",
"_rev": "1-feae33bee0b773c7d130b1dd6fb42869",
"doc_type": "standard",
"location": {
"carrier": "AT&T",
"mobileCountryCode": 123,
"mobileNetworkCode": 456
}
}
}
}

现在，我想创建一个触发器，该触发器为每行ON INSERT调用函数。

但是，我马上就挂断了电话。我已经尝试了我能想到的一切，包括：WITH、嵌套SELECT、to_json、casting：：json，我敢打赌它很简单，但为什么我(似乎(不能不使用json运算符呢？

例如，我可以直接查询记录并从该记录中获取我想要的数据(以两种不同的方式获取相同的东西(：

SELECT * FROM case_raw WHERE raw_json->'name'->'doc'->'location'->>'carrier' = 'AT&T';
SELECT * FROM case_raw WHERE raw_json @> '{"name":{"doc": {"location": { "carrier": "AT&T"}}}}'

但是，就我的一生而言，我无法踏入JSON。我至少读了20篇帖子、PG文档、PG教程，但没有找到任何能让我前进的东西。任何见解都将不胜感激。在这里，我大大缩短了使用with等的尝试。PGAdmin报告了->'name'上的错误。

CREATE OR REPLACE FUNCTION parse_raw ()
RETURNS trigger AS '
BEGIN
-- We have a lot more values to insert, but PG does not seem to like the syntax of the following:
INSERT INTO report_db.location_master VALUES (NEW.raw_json->'name'->'doc'->'location'->>'carrier');
RETURN NEW;
END' LANGUAGE 'plpgsql';
-- setup on before insert trigger to parse into tables
CREATE TRIGGER parse_raw_trigger
BEFORE INSERT ON report_db.case_raw
FOR EACH ROW
EXECUTE PROCEDURE parse_raw();

Answer 1

您正在将字符串嵌入字符串中。因此，对于嵌入的'name'，需要转义单引号。所以实际上' ... 'name' ...不是一个有效的字符串常量。应该是' ... ''name'' ...'

为了更容易，通常的方法是用美元报价包裹整个函数体(字符串(

在INSERT语句中始终限定目标列也是一种很好的编码实践。

CREATE OR REPLACE FUNCTION parse_raw ()
RETURNS trigger AS 
$body$ --<< replaces the '
BEGIN
-- We have a lot more values to insert, but PG does not seem to like the syntax of the following:
INSERT INTO report_db.location_master (carrier) VALUES (NEW.raw_json->'name'->'doc'->'location'->>'carrier');
RETURN NEW;
END;
$body$ --<< replaces the '
LANGUAGE plpgsql;