How to use json string element in a trigger function without declaring it as a variable

Question

I'm learning about Postgresql - I don't have a specific application in mind, but the general idea is to update one column with json and not worry about the rest.

I have a table with a few columns (id, name, amount, json). I would like to update only the json column and have a trigger update the other columns from the json.

For example, if json was set to {"name" = "fred", "amount" = 100}, the trigger would populate the name and amount columns

so far, this works:

create table one(id serial primary key, name text, amount int, data jsonb);

CREATE OR REPLACE FUNCTION test()
  RETURNS trigger AS
$$
DECLARE
         json_name text = (data -> 'name') from one;
         json_amount int = (data -> 'amount') from one;
BEGIN
         IF json_name IS DISTINCT FROM OLD.name THEN
         UPDATE one SET name = json_name;
         END IF;
         IF json_amount IS DISTINCT FROM OLD.amount THEN
         UPDATE one SET amount = json_amount;
         END IF;

    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER test_trigger
  AFTER INSERT OR UPDATE
  OF data ON one
  FOR EACH ROW
  EXECUTE PROCEDURE test();

I'm trying to do this without declaring any variables. for example, I've tried these:

IF one.data->'name' IS DISTINCT FROM OLD.name THEN

or

IF one.data->'name'::text IS DISTINCT FROM OLD.name THEN

or

IF ((data->'name') from one) IS DISTINCT FROM OLD.name THEN

but none work.

Making such a trigger may be a horrible idea, and that would be nice to know, but I'm mainly interested figuring out this json stuff :)

I've made an example here:

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=92f2e6dd3630c76178ca4cfe4dc30b10

sticky bit · Accepted Answer · 2019-05-05 17:12:59Z

2

Make it a before trigger and instead of an UPDATE manipulate new.

CREATE OR REPLACE FUNCTION test()
                  RETURNS trigger
AS
$$
BEGIN
  new.name := new.data->'name';
  new.amount := new.data->'amount';

  RETURN new;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER test_trigger
               BEFORE INSERT
                      OR UPDATE
                         OF data
               ON one
               FOR EACH ROW
               EXECUTE PROCEDURE test();

answered May 5, 2019 at 17:12

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user09274385 Over a year ago

Oh, this is great! I'm confused on using BEFORE - like how could something be triggered by something that hasn't happened. Is this correct: The event is started, but triggers the function before updating the table, and then the function updates everything just once? Whereas AFTER allows the table to update, and then the function updates a second time... for an inefficient two updates? Anyway, thanks - this is exactly the answer I was hoping for and more!

Ancoron Over a year ago

Yes, you basically got it right. Using BEFORE, we are actually able to modify the data row (NEW) that comes in with a query before the actual change is executed. This is one of the benefits of a row-based (O)RDBMS. In a pure column-based (O)(R)DBMS this would be a performance nightmare. ;-)

Ancoron · Accepted Answer · 2019-05-05 17:34:53Z

1

Although the answer provided by @sticky bit goes into the right direction, it doesn't handle type conversion well.

Here's a version that is better at dealing with empty JSON strings:

CREATE OR REPLACE FUNCTION test()
  RETURNS trigger AS
$$
BEGIN
    NEW.name := (NEW.data->>'name');
    NEW.amount := round((NEW.data->>'amount')::numeric)::int;
    RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER test_trigger
  BEFORE INSERT OR UPDATE
  OF data ON one
  FOR EACH ROW
  EXECUTE PROCEDURE test();

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=15a232d4092e23c7bc0425b849b31976

Please remember that executing multiple UPDATEs inside a trigger function after an original INSERT or UPDATE is going to kill performance.

edited May 5, 2019 at 17:34

answered May 5, 2019 at 17:27

Ancoron

2,7811 gold badge14 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to use json string element in a trigger function without declaring it as a variable

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related