Creation of Trigger that saves information on file

Question

I need to save in a TXT file all the closing codes made in a Postgresql database.

The table closures has the following fields:

codigo |    data    | nome
-------|------------|---------
1      | 2019-08-01 | Castor
2      | 2019-08-03 | Atlas
3      | 2019-08-05 | Nova

When a new record is inserted, I need the code of this record to be copied to a file with the code name.txt.

I know there’s a command copy of Postgresql and that when I run it, the file is created correctly with the code, but with each execution of the command the previous information of the file is overwritten and I need each new code to be in a new line of the file, that is, keeping the previous result of the execution.

Follow the command I used to do the tests with the copy (The query is test only):

COPY (SELECT MAX(codigo) FROM fechamentos) TO 'C:\Leitura\codigos.txt'

How can I create this Rigger?

Answer 1

I do not know if in postgresql it is possible to just edit a file, the logic would be to read the current file, add the new information and write it again:

I used an example txt, with the following content:

'D: test 1.txt'

0;maria
1;joao

I create a temporary table that will store the contents of the file:

create temp table txt
(
id varchar,
nome varchar
);

I upload the file to the temporary table:

copy txt from 'd:\teste\1.txt' with CSV DELIMITER ';';

I write the file again, with the new information:

copy 
(select id,nome from txt
union ALL
select '9','novo registro')
to 'd:\teste\1.txt' with CSV DELIMITER ';';

I delete the temporary table:

drop table txt;

Upshot:

0;maria
1;joao
9;novo registro

Note: I used Union ALL because in the example you can repeat the values (if you run several times, add 9;novo registro at the end of the archive)

Note: Your text file does not seem to be in a format to be read easily by the database, prefer standard formats, using delimiters.

Note: Beware of file size, rs

Logic explained above, let’s go to the creation of Trigger:

First, a Function is created that will be executed when Trigger is executed:

CREATE OR REPLACE FUNCTION public.escreve_fechamento (
)
RETURNS trigger AS
$body$
    BEGIN

        create temp table txt ( id varchar,nome varchar );
        copy txt from 'd:\teste\1.txt' with CSV DELIMITER ';';
        insert into txt (id,nome) values (new.codigo,new.nome);
        copy (select id,nome from txt) to 'd:\teste\1.txt' with CSV DELIMITER ';';
        drop table txt;

    RETURN NULL;
    END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Create the Trigger that will run after each INSERT:

CREATE TRIGGER tg_escreve_fechamento AFTER INSERT
ON public.fechamento FOR EACH ROW 
EXECUTE PROCEDURE public.escreve_fechamento();

Test:

insert into fechamento (id,nome) values (999,'Novo Fechamento');

Note: Inside the plpgsql syntax it was not possible to select with Union and the data passed in Trigger, so I made an Insert in the temporary table to then write to the file.