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?

3 Answers 3


Using 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
EXECUTE format($$COPY (SELECT %L,%L,%L,%L,%L) TO PROGRAM 'cat >> /path/to/file/my.csv'$$
             , $1,$2,$3,$4,$5); 

Why the complication with EXECUTE?

cat is a standard UNIX utility (shell command).

Your Postgres role needs appropriate privileges. The manual:

COPY naming a file or command is only allowed to database superusers or users who are granted one of the default roles pg_read_server_files, pg_write_server_files, or 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 postgres.


    text1 TEXT,
    text2 TEXT,
    text3 TEXT,
    text4 TEXT,
    text5 TEXT)
    LANGUAGE 'sql'

    COST 100
   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;

Also you need a script '/tmp/append' (which is executable by 'postgres'):

while read line
        echo $line >> /tmp/results.txt

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....

  • select $1 as text1 can be simplified to select text1 You don't need the temp table either
    – user1822
    Commented Oct 20, 2019 at 7:42
  • @a_horse_with_no_name: True, but my question than is: What use is it to add these values to a textfile? Would it not be smarter to add them to a database table, and export them as they are needed?
    – Luuk
    Commented Oct 20, 2019 at 7:51
  • @Luuk The reason I am directly adding these values to a text file is that my text file contains some comments(starting with #), in between some rows of data. Hence, I am not able to store all data of the file in a table, as comments can't be stored in that table. That is why I wanted to append the next row of data directly to the file.
    – suvrat
    Commented Oct 21, 2019 at 5:21
  • @suvrat: why don't you simply add a column to the table that stores the comment?
    – user1822
    Commented Oct 21, 2019 at 9:29

The above examples start a new unix process every time you want to write to the file.

Suppose you plan to write to the file a bunch of times during a single session, for example with a persistent connection in a web server process serving 10,000 requests before it disconnects and resets. In that case, it could be more efficient to use a PL/perl or PL/python function to write to the file. The perl or python interpreter would continue running for the duration of the session, so you wouldn't have the overhead of starting a new process every time you write a line. In principle, something like:

CREATE OR REPLACE FUNCTION public.f_dump_row(varchar,varchar,varchar,varchar,varchar)
    open(my $fh, '>>', '/path/to/log/file.csv') || die 'write to log file failed';
    print $fh join(',', @_), "\n";
$$ LANGUAGE plperl;

There is probably a way to be even more efficient by keeping the filehandle open between requests, by using a library like Log::Log4perl which does that.

Hrmm, in fact all of these examples have the problem of concurrent writes in a high-availability environment. You'd probably want to use Log::Dispatch::File::Locked instead of appending to a file. You can also use that module as the appender for a Log::Log4perl configuration to format output. I think it could be set up on session start using the postgres.conf item:

plperl.on_plperl_init = 'require "/opt/myapp/libexec/plperl_init.pl";'

... and then do whatever is needed to set up Log::Log4perl in that script so that every function that calls it won't have to initialize the log format each time. Please take this with a grain of salt as I haven't tested it yet, but I'll circle back when I do.





Hrmm... or if you want to keep it simple, you could just use elog() from a function with an identifying string, and then look for your string in the postgres log.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.