CODEWITHSUNDEEP
postgresqltriggersplpgsqlhstore
pw94
pw94

Reputation: 401

PostgreSQL update trigger Comparing Hstore values

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;

Upvotes: 4

Views: 1909

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45910

Another version - with minimalistic numbers of updates - in partially functional design (where it is possible). This trigger should be AFTER trigger, to be ensured correct behave.

CREATE OR REPLACE FUNCTION function_replication()
RETURNS trigger AS $$
DECLARE
  newh hstore;
  oldh hstore;
  update_vec text[];
  pair text[];
BEGIN
  IF new IS DISTINCT FROM old THEN
    IF new.id <> old.id THEN
      RAISE EXCEPTION 'id should be immutable';
    END IF;
    newh := hstore(new); oldh := hstore(old); update_vec := '{}';
    FOREACH pair SLICE 1 IN ARRAY hstore_to_matrix(newh - oldh)
    LOOP
      update_vec := update_vec || format('%I = %L', pair[1], pair[2]);
    END LOOP; 
    EXECUTE  
      format('UPDATE %I SET %s WHERE id = $1',
         tg_table_name || '_2',
         array_to_string(update_vec, ', '))
      USING old.id;
  END IF;
  RETURN NEW; -- the value is not important in AFTER trg 
END;
$$ LANGUAGE plpgsql;

CREATE TABLE foo(id int PRIMARY KEY, a int, b int);
CREATE TABLE foo_2(LIKE foo INCLUDING ALL);
CREATE TRIGGER xxx AFTER UPDATE ON foo
  FOR EACH ROW EXECUTE PROCEDURE function_replication();

INSERT INTO foo VALUES(1, NULL, NULL);
INSERT INTO foo VALUES(2, 1,1);
INSERT INTO foo_2 VALUES(1, NULL, NULL);
INSERT INTO foo_2 VALUES(2, 1,1);

UPDATE foo SET a = 20, b = 30 WHERE id = 1;
UPDATE foo SET a = NULL WHERE id = 1;

This code is little bit more complex, but all what should be escaped is escaped and reduce number of executed UPDATE commands. UPDATE is full SQL command and the overhead of full SQL commands should be significantly higher than code that reduce number of full SQL commands.

Upvotes: 3

klin
klin

Reputation: 121814

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;

Upvotes: 4

Related Questions