postgresql 使用db触发器更新带有key和condition的json对象

我有以下表格：

create table margins (
  id serial primary key,
  margins JSON,
  created_at TIMESTAMP NOT NULL,
  institution_uuid UUID NOT NULL,
  created_by VARCHAR
);

字符串
和

create table margin_defaults (
  id serial primary key,
  model VARCHAR(255) NOT NULL,
  margin FLOAT NOT NULL,
  created_by VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

型
我将以下对象数组存储在列margins或表margins中

insert into margins (margins, created_at, institution_uuid, created_by) VALUES ('[{"model": "samsungS23","margin": "0.5","type": "CUSTOM"},{"model": "iphone",,"margin": "0.2","type": "CUSTOM"},{"model": "pixel","margin": "0.2","type": "CUSTOM"}]', '2023-12-12 15:38:40.642428', '51d8060e-5a31-4575-b56d-c5100e94d614', 'test-runner') RETURNING *;

型
在margin_defaults表中，我存储了不同类型边距的所有默认值。此表是一个追加/创建表，因此不会更新任何行。margins表也是如此。但是，当向margin_defaults表添加新条目时，我希望能够获取margins表中每个institution_uuid的最后一组条目，并使用相同的type更新margins列中的相应对象与新的margin_defauts条目的相同，但是仅当当前裕量type被设置为DEFAULT时。
到目前为止，我已经提出了以下触发器，但我得到了这个错误：

ERROR:  function jsonb_set(jsonb, text[], double precision) does not exist
LINE 3:      jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',m...
             ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  INSERT INTO margins (institution_uuid, margins, created_by) VALUES (
                    r.institution_uuid,
                    jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',margin}')::text[], NEW.margin)::json,
                    NEW.created_by
                )
CONTEXT:  PL/pgSQL function update_margins_default_values_function() line 10 at SQL statement

型
我的触发器：

CREATE OR REPLACE FUNCTION update_margins_default_values_function()
RETURNS TRIGGER AS $$
DECLARE r RECORD;
DECLARE tmp_position int;
BEGIN
        FOR r IN
                SELECT DISTINCT ON (b.institution_uuid) * FROM (SELECT DISTINCT ON (institution_uuid) * FROM margins ORDER BY institution_uuid, created_at DESC) as b WHERE b.margins::jsonb@>'[{"type":"DEFAULT"}]' ORDER BY institution_uuid
        LOOP
            SELECT position FROM jsonb_array_elements(r.margins::jsonb) with ordinality arr(elem, position) INTO tmp_position WHERE elem->>'model'=NEW.model AND elem->>'type'='DEFAULT';
            IF found THEN
                INSERT INTO margins (institution_uuid, margins, created_by) VALUES (
                    r.institution_uuid,
                    jsonb_set(r.margins::jsonb, ('{' || tmp_position || ',margin}')::text[], NEW.margin)::json,
                    NEW.created_by
                );
            END IF;
        END LOOP;
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_margins_default_values_trigger
    AFTER INSERT ON margin_defaults FOR EACH ROW EXECUTE PROCEDURE update_margins_default_values_function();

型
一个小提琴，我试图得到工作-sqlFiddle

在阅读了对我的问题有帮助的评论后，我找到了解决办法。
SELECT position FROM jsonb_array_elements(r.margins) with ordinality arr(elem, position) INTO tmp_position返回索引从1开始的第一个值，而不是0，因此在插入回数组时需要从这个值中减去1。

CREATE OR REPLACE FUNCTION update_margins_default_values_function()
RETURNS TRIGGER AS $$
DECLARE r RECORD;
DECLARE tmp_position int;
BEGIN
        FOR r IN
                SELECT DISTINCT ON (b.institution_uuid) *
                FROM (
                        SELECT DISTINCT ON (institution_uuid) * FROM margins ORDER BY institution_uuid, created_at DESC
                ) as b WHERE b.margins::jsonb@>'[{"type":"DEFAULT"}]' ORDER BY institution_uuid
        LOOP
                SELECT position
                FROM jsonb_array_elements(r.margins::jsonb) with ordinality arr(elem, position)
                INTO tmp_position
                WHERE elem->>'model'=NEW.model AND elem->>'type'='DEFAULT';
                IF found THEN
                        INSERT INTO margins (institution_uuid, margins, created_by) VALUES (
                                r.institution_uuid,
                                jsonb_set(r.margins::jsonb, ('{' || tmp_position - 1 || ',margin}')::text[], to_jsonb(NEW.margin))::json,
                                NEW.created_by
                        );
                END IF;
        END LOOP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_margins_default_values_trigger
    AFTER INSERT ON margin_defaults FOR EACH ROW EXECUTE PROCEDURE update_margins_default_values_function();

字符串

postgresql 使用db触发器更新带有key和condition的json对象

1条答案

相关问题

热门标签

最新问答