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!

• Emilio Platzer: I suggest you to evaluate the use a table for both post_and_comments with a field 'type' that can has the value 'post' or 'comment'

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');