Write data of one column to unix file with data in another column of the same record as file name

Question

I have a task in Postgresql and I'm stuck with it. Can you please help so I can proceed with the task.

I have a table named 'JOBS' and the sample data looks as below

JOB_ID      ITEM
---------------------
1           APPLE
2           BANANA
3           CARROT

I would need to write the data in the ITEM column to unix file. The name of the file has to be the relevant JOB_ID of the corresponding ITEM record.

For e.g. File 1.txt should contain the data APPLE. File 2.txt should contain the data BANANA

Thanks in advance for your time and help.

Answer 1

I would do that using copy command within execute.

Just like this:

DO
$$
DECLARE 
    vRECORD RECORD;
    vSQL    TEXT;
    vMYPATH TEXT;
BEGIN
    -- place your path here - be sure having write permission to postgres user
    vMYPATH = '/home/christian/Temp/';

    -- loop your records saving each record to a single file
    FOR vRECORD IN SELECT JOB_ID, ITEM FROM JOBS ORDER BY JOB_ID
    LOOP
        vSQL = 'COPY (SELECT ''' || vRECORD.ITEM || ''' AS ITEM) TO ''' || vMYPATH || vRECORD.JOB_ID || '.txt''' ; 
        RAISE NOTICE 'SQL=%', vSQL;
        EXECUTE vSQL;
    END LOOP;

END;
$$
;