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.
wouldn’t it be possible to do this via application? the ideal is whenever possible to avoid triggers.
– danilo
@Anilo, no, the application is third party.
– Laércio Lopes
https://stackoverflow.com/questions/11056546/output-to-file-from-pgsql-trigger I think you’ll have to ask the community to add this functionality to a new version of postgresql
– danilo
But a copy can be used!
– Laércio Lopes
https://tada.github.io/pljava/use/hello.html you can try calling a batch via pljava on Trigger
– danilo
If you want to manipulate files in some procedural language in a DBMS Postgresql search for UNTRUSTED Languages.
– anonimo
https://raghavt.blog/install-pl-java-1-5-2-in-postgresql-11/ to use pljava you will need to compile and run its installer which copies the libraries in your postgresql, restart postregsql, do all this on a test server before, test well, have a whole contingency and fallback plan when doing in production.
– danilo