0

Good afternoon There is a label in which records are added. Each entry has a json column. Json has several 6 fields. When adding a new record, the data from it may refer to the old record. It is necessary to catch this with a trigger and fill in from the old record.

My code:

CREATE FUNCTION insert_customized_agreement_list_item_row() RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
    DECLARE
        object jsonb;
    BEGIN
        UPDATE customized_agreement_list_item SET event = 'deleted' WHERE agreement_id = NEW.agreement_id;
        NEW.row_id = nextval('shared_row_id_seq');
        NEW.last_modified = now();
        
        object = NEW.object;

        IF (object #>> '{customized_agreement_list_item, body, color}') IS NULL THEN
            object = jsonb_set(object, '{customized_agreement_list_item, body, color}', OLD.object #> '{customized_agreement_list_item, body, color}');

        END IF;
        IF (object #>> '{customized_agreement_list_item, body, custom_name}') IS NULL THEN
    object = jsonb_set(object, '{customized_agreement_list_item, body, custom_name}', OLD.object #> '{customized_agreement_list_item, body, custom_name}');
        END IF;
        IF (object #>> '{customized_agreement_list_item, body, custom_rank}') IS NULL THEN
    object = jsonb_set(object, '{customized_agreement_list_item, body, custom_rank}', OLD.object #> '{customized_agreement_list_item, body, custom_rank}');
        END IF;
        IF (object #>> '{customized_agreement_list_item, body, is_hidden}') IS NULL THEN
    object = jsonb_set(object, '{customized_agreement_list_item, body, is_hidden}', OLD.object #> '{customized_agreement_list_item, body, is_hidden}');
        END IF;
        IF (object #>> '{customized_agreement_list_item, body, klogin}') IS NULL THEN
    object = jsonb_set(object, '{customized_agreement_list_item, body, klogin}', OLD.object #> '{customized_agreement_list_item, body, klogin}');
        END IF;
        IF (object #>> '{customized_agreement_list_item, body, subject_id}') IS NULL THEN
    object = jsonb_set(object, '{customized_agreement_list_item, body, subject_id}', OLD.object #> '{customized_agreement_list_item, body, subject_id}');
        END IF;
        
        NEW.object = object;
RETURN NEW;
END;
$BODY$;

But it doesn't work. The data from the old record does not go to the new one. What could be wrong?

1 Answer 1

1

I believe the variable assignment you are using is incorrect for example:

object = NEW.object;

The correct way of assigning the variable is:

object := NEW.object;

For more information on this check out the Postgres docs - https://www.postgresql.org/docs/current/plpgsql-statements.html

Also, make sure that you have registered the trigger using the below:

CREATE TRIGGER json_filling_in_trigger AFTER INSERT ON <TABLE NAME>
FOR EACH ROW
EXECUTE FUNCTION insert_customized_agreement_list_item_row();

Please ensure you fill in the correct table name.

The docs on registering the triggers can be found here - https://www.postgresql.org/docs/current/plpgsql-trigger.html

Hope that helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.