Insert newline in text file using COPY

Question

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

`E'...'` in Postgres is escape string syntax, see here [Escape string](https://www.postgresql.org/docs/current/runtime-config-compatible.html). Please change your title and question to reflect this. Also add to your question the table schema you are copying from. Also `COPY` is for outputting data not formatted strings. Also you cannot concatenate a `date` field(CURRENT_DATE) to a string in any case. — Adrian Klaver, Aug 26 '21 at 21:59
the schema for the time being is public. and if Copy is for outputting data, what is the way to output frmatted strings. for the current date the line of code in my function works just fine in putting the date with whatever string that i put after it, so unless i misinterpreted your reply i'm not sure what you want to say — Majd, Aug 26 '21 at 22:14
@AdrianKlaver I'm not using any tables in my example, i just want to be able to generate formatted text or csv dcuments using plpgsql in a similiar way to sqlplus — Majd, Aug 26 '21 at 22:18
I have already told you that can't be done using `COPY`. You can create a straight CSV file, but that is it. If you want to create a report you will need to use something else. — Adrian Klaver, Aug 26 '21 at 22:25
That is going to depend on what output format you want and what programming language(s) you are comfortable with. It is really out of the scope of a SO answer. Search on Postgres report and the language of your choice. — Adrian Klaver, Aug 26 '21 at 22:50

score 1 · Accepted Answer · answered Aug 27 '21 at 02:20

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

Insert newline in text file using COPY

1 Answers1