CODEWITHSUNDEEP
jsonpostgresqltriggerspostgresql-9.3
Lunigorn
Lunigorn

Reputation: 1422

Change JSON by trigger in PostgreSQL 9.3

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;

Upvotes: 2

Views: 1252

Answers (1)

pozs
pozs

Reputation: 36274

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

Upvotes: 1

Related Questions