I am trying to create a trigger for an update on jsonb column in Postgresql 13.7

Question

CREATE OR REPLACE FUNCTION update()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    with name_array as (select jsonb_array_elements(inputs #> '{lists}')  AS lists
    from random.model
                             )
                             select name_array.lists #>> '{name}' as name,
                                    name_array.lists #>> '{value}' as value
                                    from name_array;

    IF NEW.value   <> OLD.value THEN
         INSERT INTO random.model_tracker(userid,modelid,datetime,oldjsoninput,newjsoninput,action)
         Values (old.user_id,old.id,now(),
                 '{"lists": [{"name": "OLD.name"}, {"value": "OLD.value"}]}'
                 ,'{"lists": [{"name": "NEW.name"},{"value": "NEW.value"}]}'
                 ,'UPDATE');
    END IF;

    RETURN NEW;
END;
$$

Trigger

CREATE TRIGGER update AFTER UPDATE ON random.model FOR EACH ROW EXECUTE PROCEDURE update();

When i am running inner query, it produces outputs as text for name and value. This function gets created however when i update values on the table i am getting this error:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function update() line 6 at SQL statement SQL state: 42601

Answer 1

any select inside of a function need to be placed inside of a variable, that was the error message. made another version of the json :

CREATE OR REPLACE FUNCTION update()
  RETURNS TRIGGER 
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
 
    IF NEW.value   <> OLD.value THEN
         INSERT INTO random.model_tracker(userid,modelid,datetime,oldjsoninput,newjsoninput,action)
         Values (old.user_id,old.id,now(),
                 (SELECT * FROM (WITH lists (lists ) AS (
 WITH q ( name , value ) AS
 ( SELECT OLD.name , OLD.value )  SELECT array_to_json( ARRAY[row_to_json(q)] ) FROM q 
)SELECT row_to_json(list.*) FROM lists
)) , 
(SELECT * FROM (
 WITH lists (lists ) AS (
 WITH q ( name , value ) AS
 ( SELECT NEW.name , NEW.value )  SELECT array_to_json( ARRAY[row_to_json(q)] ) FROM q 
)SELECT row_to_json(list.*) FROM lists
))   ,'UPDATE');
    END IF;

    RETURN NEW;
END;
$$