[SOLVED] Insert newline in text file using COPY

Insert newline in text file using COPY

I want to insert a line break in a text file using copy command

I'm asked to port sqlplus scripts that generate reports from the data base in custom csv and text formats. I'm looking for ways to implement functionalities of sqlplus like:

set newpage
set linesize
spool <File>
ttitle left 'text....'
center 'some text....'

some queries 

exit

For the simple task f trying to use copy in plpgsql I'm trying t print a multiline string to a file but the E mode for insert line is not working

My line:

COPY (SELECT CURRENT_DATE || E'\n' || 'NAME: MY NAME' || E'\n' || 'ADDRESS:ADDRESS') TO 'path/to/file.txt');

I tried the other way of putting the E in the beginning, didn't work too.

Can anyone help me with this and how o approach doing what sqlplus is doing in postgres ?

I'm using debian and postgres 13.3

Solution

COPY doesn't work here, because it will escape line breaks.

You are using the client (sqlplus) for that in Oracle, so do the same thing in PostgreSQL and use psql:

\pset tuples_only
\pset format unaligned
\o path/to/file.txt
SELECT CURRENT_DATE || E'\n' || 'NAME: MY NAME' || E'\n' || 'ADDRESS:ADDRESS';
\q