CODEWITHSUNDEEP
postgresqltriggerssql-update
Fred B
Fred B

Reputation: 142

Update newly created row value with a trigger

My problem is as follow: I insert or update a row in a postgresql database and need to modify one field in this row. BUT I need to know the new serial PK when I insert a new row to make a SELECT with JOIN on other tables.

I'm now stucked because I've done a AFTER INSERT AND UPDATE trigger to get the new PK (kkw_block_id). I get the value I need with the SELECT but after that I can't modify the value in the row: modifying the NEW.value is not possible with AFTER INSERT AND UPDATE and if I do an UPDATE on the row, I enter in an infinite loop, the trigger beeing called in the trigger...

CREATE TRIGGER tsvectorupdate
  AFTER INSERT OR UPDATE
  ON kkw_block
  FOR EACH ROW
  EXECUTE PROCEDURE kkw_search_trigger();

CREATE OR REPLACE FUNCTION kkw_search_trigger()
  RETURNS trigger AS
$BODY$

DECLARE vector_en TEXT;
  DECLARE vector_fr TEXT;
  DECLARE vector_de TEXT;
    BEGIN   
        -- I need the new serial PK(kkw_id) in the following section.
        SELECT coalesce(modell_en, '') || ', ' || coalesce(bezeichnung_en,'') || ', ' || coalesce(kkw.kkw_name_en,'') || ', ' || coalesce(kkw_typ.typ_abr,'') || ', ' || coalesce(kkw_typ.typ_desc_en,'') || ', ' || coalesce(kkw_typ.typ_desc_short_en,'') INTO vector_en
        FROM kkw_block
        LEFT JOIN kkw ON NEW.kkw_id = kkw.kkw_id
        LEFT JOIN kkw_typ ON NEW.kkw_typ_id = kkw_typ.kkw_typ_id
        WHERE kkw_block_id = NEW.kkw_block_id;

        -- I need to update a field of the newly created or updated row. 
        NEW.search_vector_en := to_tsvector('english', 'new test vector'); --- This doesn't work with 'AFTER UPDATE' trigger.

        RETURN NULL;
    END
$BODY$

Any idea?

Upvotes: 0

Views: 858

Answers (2)

Fred B
Fred B

Reputation: 142

I end up with the following solution. I made a BEFORE trigger. The problem was the LEFT JOIN with reference to the table where the new row doesn't exist yet. It's not ideal but here it is:

CREATE TRIGGER tsvectorupdate
  BEFORE INSERT OR UPDATE
  ON kkw_block
  FOR EACH ROW
  EXECUTE PROCEDURE kkw_search_trigger();

CREATE TYPE kkw_type_record_type AS (typ_abr TEXT, typ_desc_en TEXT, typ_desc_short_en TEXT);
CREATE TYPE kkw_record_type AS (kkw_name_en TEXT);

CREATE OR REPLACE FUNCTION kkw_search_trigger()
  RETURNS trigger AS
$BODY$

  DECLARE kkw_rec kkw_record_type;
  DECLARE kkw_typ_rec kkw_type_record_type;

  DECLARE vector_en TEXT;

    BEGIN   
        --- make a individual select instead of LEFT JOIN
        SELECT kkw_name_en  INTO kkw_rec.kkw_name_en
        FROM kkw
        WHERE kkw.kkw_id = NEW.kkw_id;

        --- make a individual select instead of LEFT JOIN
        SELECT typ_abr, typ_desc_en, typ_desc_short_en INTO kkw_typ_rec.typ_abr, kkw_typ_rec.typ_desc_en, kkw_typ_rec.typ_desc_short_en
        FROM kkw_typ
        WHERE kkw_typ.kkw_typ_id = NEW.kkw_typ_id;

        vector_en := coalesce(NEW.modell_en, '') || ', ' || coalesce(NEW.bezeichnung_en,'') || ', ' || coalesce(kkw_rec.kkw_name_en,'') || ', ' || coalesce(kkw_typ_rec.typ_abr,'') || ', ' || coalesce(kkw_typ_rec.typ_desc_en,'') || ', ' || coalesce(kkw_typ_rec.typ_desc_short_en,'');

        NEW.search_vector_en := to_tsvector('english', vector_en);          
        RETURN NEW;
    END
$BODY$

Upvotes: 0

Łukasz Kamiński
Łukasz Kamiński

Reputation: 5930

Drop default for your PK and assign it in your BEFORE trigger. You will have to change that existing trigger from AFTER to BEFORE.

You can assign PK from sequence like that:

NEW.kkw_block_id = nextval('your_sequence_name_here');

Since you are using the same function for both INSERT and DELETE, you need to check if it is INSERT and only then use sequence. I have also included check if PK is null or not. I suppose that alone would be enough to not overwrite it during update.

IF (TG_OP = 'INSERT') AND NEW.kkw_block_id IS NULL THEN
  NEW.kkw_block_id = nextval('your_sequence_name_here');
END IF;

This will be fine as long as this trigger will work for each new row, with seems to be the case. This will let you modify NEW and it will be reflected in data saved in table.

Upvotes: 2

Related Questions