I have a text file which contains some rows of data,say 10 rows. Each row has 5 columns, each of the type varchar. Is it possible to write a function in postgresql, which takes its parameters as 5 varchar values, and appends those values to that file directly?
COPY, this boils down to one simple SQL command:
COPY (SELECT 'a','b','c','d','e') TO PROGRAM 'cat >> /path/to/file/my.csv';
You can wrap it into a function if required:
CREATE OR REPLACE FUNCTION public.f_dump_row(varchar,varchar,varchar,varchar,varchar) RETURNS void LANGUAGE plpgsql AS $func$ BEGIN EXECUTE format($$COPY (SELECT %L,%L,%L,%L,%L) TO PROGRAM 'cat >> /path/to/file/my.csv'$$ , $1,$2,$3,$4,$5); END $func$;
Why the complication with
cat is a standard UNIX utility (shell command).
Your Postgres role needs appropriate privileges. The manual:
COPYnaming a file or command is only allowed to database superusers or users who are granted one of the default roles
pg_execute_server_program, since it allows reading or writing any file or running a program that the server has privileges to access.
If you need this for under-privileged roles, consider a
SECURITY DEFINER function. But do it properly as instructed in the manual to avoid misuse.
And the target file must be writable by the system user running the Postgres process, typically
CREATE OR REPLACE FUNCTION public."AddFiveValues"( text1 TEXT, text2 TEXT, text3 TEXT, text4 TEXT, text5 TEXT) RETURNS TEXT LANGUAGE 'sql' COST 100 VOLATILE AS $BODY$ CREATE TEMP TABLE mytable AS SELECT $1 as text1,$2 as text2,$3 as text3,$4 as text4,$5 as text5; COPY mytable TO PROGRAM '/tmp/append'; DROP TABLE mytable; SELECT '1'; $BODY$;
Also you need a script '/tmp/append' (which is executable by 'postgres'):
#!/bin/bash while read line do echo $line >> /tmp/results.txt done
SELECT public."AddFiveValues"( '1', '2', '3', '4', '5'); will add one line to '/tmp/results.txt'.
Filename(s) can (or should?) be changed as needed....