CODEWITHSUNDEEP
postgresqlpostgresql-11
user09274385
user09274385

Reputation: 55

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

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

Upvotes: 1

Views: 696

Answers (2)

Ancoron
Ancoron

Reputation: 2733

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.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

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();

Upvotes: 2

Related Questions