1

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?

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?

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:

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

  • select $1 as text1 can be simplified to select text1 You don't need the temp table either – a_horse_with_no_name Oct 20 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 Oct 20 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 Oct 21 at 5:21
  • @suvrat: why don't you simply add a column to the table that stores the comment? – a_horse_with_no_name Oct 21 at 9:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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