Reputation: 1422
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
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