Add data to second table on UPDATE (PostgreSQL only)

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

-

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.

-

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

• Create a boolean on the row for triaged or whatever the transfere_table represents.

• 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.

-
Create the table transfere_done

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

-
Copy over all the ids to it.

-
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.

StackExchange Database Administrators Q#173825, answer score: 2