Change JSON by trigger in PostgreSQL 9.3

Question

I have some sequence and table:

CREATE SEQUENCE test_uid start 1;

CREATE TABLE test (
  some_data JSON
);

How can I make trigger which add id field by sequence and modified field by timestamp with the base distribution of PostgreSQL 9.3 and low performance penalty?

I need trigger something like this:

CREATE OR REPLACE FUNCTION set_id_and_modified() RETURNS TRIGGER AS $set_id_and_modified$
BEGIN
  NEW.some_data[modified] = CURRENT_TIMESTAMP;
  NEW.some_data[id] = test_uid.getNext();
  RETURN NEW;
END
$set_id_and_modified$ LANGUAGE plpgsql;

pozs · Accepted Answer · 2014-05-05 08:40:08Z

You cannot set the properties in a json independently in PostgreSQL. What you can do is manipulating the whole json field & set its results to your some_data field.

For manipulating an existing json value (i found the most constructive way is) just split it to pairs with json_each() & build up the json object manually:

DECLARE
  "results" TEXT[] DEFAULT '{}';
  "pairs" CURSOR FOR
    SELECT *
    FROM json_each(NEW."some_data")
    WHERE "key" NOT IN ('id', 'modified')
    UNION ALL
    SELECT 'id', to_json(test_uid.getNext())
    UNION ALL
    SELECT 'modified', to_json(CURRENT_TIMESTAMP);
BEGIN
  FOR "pair" IN "pairs" LOOP
    "results" = array_append(
      "results",
      to_json("pair"."key")::TEXT || ':' || "pair"."value"::TEXT
    );
  END LOOP;
  NEW."some_data" = '{' || array_to_string("results", ',') || '}';
  RETURN NEW;
END

Collectives™ on Stack Overflow

Change JSON by trigger in PostgreSQL 9.3

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related