Test datatype on postgresql update trigger

Question

I use a Trigger to detect if at least one value has changed when an update is performed on a table.

The function :

CREATE OR REPLACE FUNCTION update_version_column()
  RETURNS trigger AS
$BODY$
BEGIN
    IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
        NEW.version = now(); 
        RETURN NEW;
    ELSE
        RETURN OLD;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_version_column()
  OWNER TO gamesplateform;

The problem is, when a column type is "json", the "is distinct" failed (standard comportement, explained in the documentation).

I search a way to perform the same thing, but if a column is in "json" type, I want to force the "jsonb" cast which accept comparison operator.

Is there a way to do this ?

Thanks !

Answer 1

Found a way to do this :

CREATE OR REPLACE FUNCTION update_version_column()
  RETURNS trigger AS
$BODY$

DECLARE
    updated boolean := false;
    column_data record;

BEGIN

    FOR column_data IN 
        SELECT
        column_name::varchar,
        data_type::varchar
        FROM information_schema.columns AS c
        WHERE table_name = TG_TABLE_NAME
    LOOP
        IF column_data.data_type = 'json' THEN
            EXECUTE format('SELECT CASE WHEN ($1).' || column_data.column_name || '::text <> ($2).' || column_data.column_name || '::text THEN true ELSE false END') 
            USING NEW, OLD
            INTO updated;
        ELSE
            EXECUTE format('SELECT CASE WHEN ($1).' || column_data.column_name || ' <> ($2).' || column_data.column_name || ' THEN true ELSE false END')
            USING NEW, OLD
            INTO updated;
        END IF;
        IF updated = true THEN
            NEW.version := now();
            RETURN NEW;
        END IF;
    END LOOP;

    RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_version_column()
  OWNER TO gamesplateform;

I cast the "json" in "text" because I have a fail with "jsonb" (type does not exist). Seems to be specific to my configuration, but this trigger answers great :)

Thanks @Julien Bossart !