PostgreSQL pass table name and column name as arguments to function

Question

I have two almost similar tables: posts and comments.

Both of them has column id and votes.

Also i have two tables posts_votes and comments_votes that also has an almost similar structure: both of them has column value (can be -1 or +1), has user_id as FOREIGN KEY to user_account table, and field post_id or comment_id depending on which table it is, for posts_votes it will be post_id, and for comments_votes it will be comment_id.

What i need, is to write a trigger that will update votes counter for posts and comments table each time the row inserted/deleted/updated in posts_votes or comments_votes table.

It can be easily done by writing to different triggers, with difference only in table name for update query, and column name for WHERE statement.

This is example of such trigger for posts table (full DDL here):

CREATE OR REPLACE FUNCTION update_votes_counter() RETURNS TRIGGER AS
$$
  BEGIN;
    IF (TG_OP = 'INSERT') THEN
      UPDATE posts SET votes = votes + NEW.value WHERE id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE posts SET votes = votes - OLD.value WHERE id = OLD.post_id;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE posts SET votes = votes - OLD.value + NEW.VALUE WHERE id = NEW.post_id;
    END IF;
  END;
$$
LANGUAGE plpgsql;

What i want, is to write single function with arguments as table name and column with reference, so i can use this function in both cases for two tables.

It will be like this:

update_votes_counter('posts', 'post_id');
update_votes_counter('comments', 'comment_id');

and the function should build query like this:

UPDATE $table_name$ SET votes = votes + NEW.value WHERE id = NEW.$column_name$

Thanks for any advices!

Answer 1

Yo can create the functions dynamicaly to get a pair of static triggers doing this:

DO
$COMMAND$
declare
  v_sql text:=
$CREATES$
CREATE OR REPLACE FUNCTION update_votes_in_posts() RETURNS TRIGGER AS 
$$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      UPDATE posts SET votes = votes + NEW.value WHERE id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE posts SET votes = votes - OLD.value WHERE id = OLD.post_id;
      return old;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE posts SET votes = votes - OLD.value + NEW.value WHERE id = NEW.post_id;
    END IF;
    return new;
  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_votes_in_posts 
  AFTER INSERT OR DELETE OR UPDATE ON posts_votes
  FOR EACH ROW
  EXECUTE PROCEDURE update_votes_in_posts();
$CREATES$;
begin
  execute v_sql;
  execute replace(v_sql, 'post', 'comment');
end;
$COMMAND$;

In one command (the DO) you set a variable (v_sql) that it contains the trigger for the posts table. You can execute the content of that variable directly. Then you can replace the inside the variable content all ocurrences of post (replacing with comment).

¡Voilá!

Look at a running complete example of this in: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=f6c9931bc985f3ee7ebec7ee51c5017f

Answer 2

As one guy in Telegram messenger said to me, it can be done also in this way:

CREATE OR REPLACE FUNCTION update_votes_counter() RETURNS TRIGGER AS
  $$
  DECLARE
    entity_id INT;
    value SMALLINT;
  BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN 
        EXECUTE format('SELECT ($1).%I', TG_ARGV[1]) INTO entity_id USING NEW;
    ELSE
        EXECUTE format('SELECT ($1).%I', TG_ARGV[1]) INTO entity_id USING OLD;
    END IF;

    IF (TG_OP = 'INSERT') THEN
      value = NEW.value;
    ELSIF (TG_OP = 'DELETE') THEN
      value = -OLD.value;
    ELSIF (TG_OP = 'UPDATE') THEN
      IF (OLD.value = NEW.value) THEN
        RETURN NEW;
      END IF;

      value = -OLD.value + NEW.value;
    end if;

    EXECUTE format('UPDATE %I SET votes = votes + $1 WHERE id = $2', TG_ARGV[0]) USING value, entity_id;

    RETURN NEW;
  end;
  $$
LANGUAGE plpgsql;

CREATE TRIGGER posts_votes_updates AFTER INSERT OR UPDATE OR DELETE ON posts_votes FOR EACH ROW EXECUTE PROCEDURE update_votes_counter('posts', 'post_id');