Is it possible to write a function in postgresql which directly writes its parameters to a text file?

Question

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?

Answer 1

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
$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 EXECUTE?

• How to set column DEFAULT using value of a variable

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.

Related:

• copy command in postgresql to append data

Answer 2

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

Answer 3

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)
  RETURNS void LANGUAGE AS
$$
    open(my $fh, '>>', '/path/to/log/file.csv') || die 'write to log file failed';
    print $fh join(',', @_), "\n";
    close($fh);
    return;
$$ 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.

References:

https://www.postgresql.org/docs/current/plperl-funcs.html

https://www.perlmonks.org/?node_id=860804

https://metacpan.org/pod/Log::Dispatch::File

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.