PostgreSQL update trigger Comparing Hstore values

Question

I am creating trigger in PostgresSQL. On update I would like to compare all of the values in a Hstore column and update changes in my mirror table. I managed to get names of my columns in variable k but I am not able to get values using it from NEW and OLD.

CREATE OR REPLACE FUNCTION function_replication() RETURNS TRIGGER AS
$BODY$
DECLARE
    k text;
BEGIN
        FOR k IN SELECT key FROM EACH(hstore(NEW)) LOOP
            IF NEW.k != OLD.k THEN
                EXECUTE 'UPDATE ' || TG_TABLE_NAME || '_2' || 'SET ' || k || '=' || new.k || ' WHERE ID=$1.ID;' USING OLD;
            END IF;
        END LOOP;
        RETURN NEW;
END;
$BODY$
language plpgsql;

Answer 1

You should operate on hstore representations of the records new and old. Also, use the format() function for better control and readibility.

create or replace function function_replication() 
returns trigger as
$body$
declare
    newh hstore = hstore(new);
    oldh hstore = hstore(old);
    key text;
begin
    foreach key in array akeys(newh) loop
        if newh->key != oldh->key then
            execute format(
                'update %s_2 set %s = %L where id = %s',
                tg_table_name, key, newh->key, oldh->'id');
        end if;
    end loop;
    return new;
end;
$body$
language plpgsql;