PSQL add line breaks to a string

Question

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?

Laurenz Albe · Accepted Answer · 2020-06-13 14:57:32Z

2

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.

answered Jun 13, 2020 at 14:57

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adrian Klaver · Accepted Answer · 2020-06-13 16:13:59Z

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.

Collectives™ on Stack Overflow

PSQL add line breaks to a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related