CODEWITHSUNDEEP
postgresql
nimgwfc
nimgwfc

Reputation: 1499

PSQL add line breaks to a string

I have a function in PSQL that sends an email to a particular user. The function takes an email text argument and adds this to some constant text before and after in the email body:

In this function, I have a json_build_object call which deals with the recipient, body and title of the email:

jsonb_build_object(
            'recipient',recipient,
            'title',title,
            'message_text', 'some text before.
             some text on new line ' || body ||
            ' some text after.
             some text on new line.'
        )

My problem is, in the text before and after body, I can't format this on to separate lines. I have tried the following but it did not work:

jsonb_build_object(
            'recipient',recipient,
            'title',title,
            'message_text', E'some text before.\n
             some text on new line \n' || body ||
            E'\nsome text after.\n
             some text on new line.'
        )

Can anyone advise what I am doing wrong?

Upvotes: 1

Views: 2451

Answers (2)

Adrian Klaver
Adrian Klaver

Reputation: 19570

Laurenz Albe pointed out the issue from the JSON end. My answer is about the other end.

What are you doing with the JSON to get it into an email?

It might be possible to do what you want from there. An example in Python:

select * from json_build_object(
            'recipient', 'aklaver',
            'title', 'head bottlewasher',
            'message_text', 'some text before.\nsome text on new line ' || ' The body'||' some text after.\nsome text on new line.'
        );
                                                                             json_build_object                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {"recipient" : "aklaver", "title" : "head bottlewasher", "message_text" : "some text before.\\nsome text on new line  The body some text after.\\nsome text on new line."}

email_str = "some text before.\\nsome text on new line  The body some text after.\\nsome text on new line." 

print(email_str.replace("\\n", "\n"))                                                                                                                                      
some text before.
some text on new line  The body some text after.
some text on new line.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246093

You do it correctly, and the result has line breaks in the form \n. That's how line breaks in text look in JSON.

RFC 8259 describes this:

A string begins and ends with quotation marks. All Unicode characters may be placed within the quotation marks, except for the characters that MUST be escaped: quotation mark, reverse solidus, and the control characters (U+0000 through U+001F).

Line feed is character U+000A, so it must be escaped.

If you want unescaped line breaks, use something else than JSON.

Upvotes: 2

Related Questions