postgresqltriggersplpgsql

Postgresql- trigger to insert and then to truncate table


I'm using a transition table to import rows in a source table after collecting data with Qfield on the transition table.

This function works well to import rows:

CREATE OR REPLACE FUNCTION shema.function_name()
    RETURNS trigger LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    INSERT INTO shema.source_table (field1, field2, field3)
    VALUES(NEW.*);

    RETURN new;
END; $BODY$;

Here is the trigger (inside the transition table)

CREATE OR REPLACE TRIGGER trigger_name
    AFTER INSERT
    ON shema.transition_table
    FOR EACH ROW
    EXECUTE FUNCTION shema.function_name();

It's working well but now I want to truncate the transition_table after the import. My goal is to achieve these two steps:

  1. export rows from transition_table to source_table
  2. delete all rows in transition_table

So I'll be able to go back to the field with my application, collect some new data in the empty transition_table and do it again.

I've tried many things but it's a fail. I'm a beginner with trigger.


Solution

    1. Use a per-statement trigger so that it just fires once for each insert, not for each incoming row. REFERENCING NEW TABLE AS newtab also lets you move all those rows at once as if they were in a special table.

    2. Use an unqualified delete to wipe the table the trigger is attached to. It has the same effect as truncate:

      TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE

      but unlike truncate, you can use it there without a problem.

    demo at db<>fiddle

    CREATE OR REPLACE FUNCTION shema.function_name()
        RETURNS trigger LANGUAGE 'plpgsql'
    AS $BODY$
    BEGIN
        INSERT INTO shema.source_table(field1, field2, field3)TABLE newtab;
        --`truncate` isn't allowed for `transition_table` in this context
        --use unqualified `delete` instead
        DELETE FROM shema.transition_table;
        
        RETURN null;
    END; $BODY$;
    
    CREATE OR REPLACE TRIGGER trigger_name
        AFTER INSERT
        ON shema.transition_table
        REFERENCING NEW TABLE AS newtab
        FOR EACH STATEMENT
        EXECUTE FUNCTION shema.function_name();