Add data to second table on UPDATE (PostgreSQL only)

Question

I have table ticket which has these (and other) columns:

• id
• ticket_type
• client

If ticket_type or client gets updated, then the table transfere_table should get filed. The ticket.id of the changed ticket should get inserted into the transfer table.

Here is the working code:

CREATE FUNCTION add_ticket_to_transfere_table () RETURNS trigger 
 LANGUAGE plpythonu AS $$
plpy.execute(plpy.prepare(
'''INSERT INTO transfere_table (ticket_id) VALUES (1ドル)''',
 ['int']), [TD['new']['id']])
$$;
CREATE TRIGGER add_ticket_to_transfere_table_trigger 
 AFTER UPDATE OF ticket_type_id, client_id ON ticket
 FOR EACH ROW
 WHEN (OLD.ticket_type_id != NEW.ticket_type_id or 
 OLD.client_id != NEW.client_id)
 EXECUTE PROCEDURE add_ticket_to_transfere_table();

Since I only used plpythonu up to now, I solved it like this. It works, but I see two things to improve:

• get rid of FOR EACH ROW: If N rows get updated, it would be nice if only one INSERT gets executed
• Don't use plpythonu

How to realize these improvements?

Are there other things which could get improved?

I am using PostgreSQL 9.3.13

• András Váczi: Well, use PL/pgSQL instead, that solves the second point. The first one will be solved in PostgreSQL 10.0, until then you need FOR EACH ROW.
• Evan Carroll: @dezso interesting, today, I learned about REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ]
• Evan Carroll: Let's make this question future-safe then, what version of PostgreSQL are you using guettll?
• guettli: @EvanCarroll PostgreSQL 9.3.13, I updated the question.

Answer 1

1. get rid of FOR EACH ROW: If N rows get updated, it would be nice if only one INSERT gets executed

   How would that work, you wouldn't have access to OLD or NEW, because it'd be a statement-trigger.

2. Don't use plpythonu

   Python natively doesn't support running as a trusted language. You can easily use plpgsql.

Code for a plpgsql function,

CREATE OR REPLACE FUNCTION add_ticket_to_transfere_table()
RETURNS TRIGGER
AS $$
 BEGIN
 INSERT INTO transfere_table (ticket_id) VALUES (new.id);
 RETURN new;
 END;
$$
LANGUAGE plpgsql;

This isn't how I would personally run this though. What I would do is

1. Create a boolean on the row for triaged or whatever the transfere_table represents.
2. When you insert, have that Boolean default to true. Effectively a column-trigger if no data is provided. When you update the ticket, have that boolean set in the update statement.

If you don't want to touch that the table itself, I'd run your process in reverse.

1. Create the table transfere_done

   CREATE TABLE transfere_done (
    ticket_id int PRIMARY KEY REFERENCES ticket.ticket_id
   )
   

2. Copy over all the ids to it.

3. Create the below view,

   CREATE VIEW transfere_queue
   AS
    SELECT * FROM ticket
    WHERE NOT EXISTS (
    SELECT 1
    FROM transfere_done
    WHERE transfere_done.id = ticket.id
    );
   

Now, when you insert into ticket, it automagically shows up under transfere_queue until you insert into transfere_done. You may or may not want to MATERIALIZE the view. Either way, I really hate triggers when not needed and I try to engineer around them.

• guettli: You would do it different? I don't understand it 100%. You would call UPDATE instead of INSERT? This would mean I would have a row in "transfere_table" for every row in the ticket table. The ticket table is big, and the transfere table is small. The transfere table should only contain the rows of all transfers (outside postgres) which still need to be done.
• Evan Carroll: No, I would delete transfere_table entirely. If it's just storing ids of rows in other tables, just put an indexed boolean on that row directly. IE is_transfere or whatever.
• guettli: yes, now I understand you. In my case the table ticket is from one application, and the transfere table is from an other application. I would like to avoid that the plugins change the database schema of the core application. Or do you have a better solution?
• guettli: @EvanCaroll I got this err msg "Control reached end of trigger procedure without RETURN". After adding "return new;" it works. Thank you this is much simpler than using plpythonu.
• guettli: I added "RETURN new".