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:
transition_table
to source_table
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.
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.
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 unqualifiedDELETE
but unlike truncate
, you can use it there without a problem.
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();