Re: Emitting JSON to file using COPY TO

On 12/4/23 17:55, Davin Shearer wrote:
> Sorry about the top posting / top quoting... the link you sent me gives
> me a 404.  I'm not exactly sure what top quoting / posting means and
> Googling those terms wasn't helpful for me, but I've removed the quoting
> that my mail client is automatically "helpfully" adding to my emails.  I
> mean no offense.

No offense taken. But it is worthwhile to conform to the very long
established norms of the mailing lists on which you participate. See:

https://en.wikipedia.org/wiki/Posting_style

I would describe the Postgres list style (based on that link) as

"inline replying, in which the different parts of the reply follow
the relevant parts of the original post...[with]...trimming of the
original text"

> There are however a few characters that need to be escaped

> 1. |"|(double quote)
> 2. |\|(backslash)
> 3. |/|(forward slash)
> 4. |\b|(backspace)
> 5. |\f|(form feed)
> 6. |\n|(new line)
> 7. |\r|(carriage return)
> 8. |\t|(horizontal tab)
>
> These characters should be represented in the test cases to see how the
> escaping behaves and to ensure that the escaping is done properly per
> JSON requirements.

I can look at adding these as test cases. The latest version of the
patch (attached) includes some of that already. For reference, the tests
so far include this:

8<-------------------------------
test=# select * from copytest;
style | test | filler
---------+----------+--------
DOS | abc\r +| 1
| def |
Unix | abc +| 2
| def |
Mac | abc\rdef | 3
esc\ape | a\r\\r\ +| 4
| \nb |
(4 rows)

test=# copy copytest to stdout (format json);
{"style":"DOS","test":"abc\r\ndef","filler":1}
{"style":"Unix","test":"abc\ndef","filler":2}
{"style":"Mac","test":"abc\rdef","filler":3}
{"style":"esc\\ape","test":"a\\r\\\r\\\n\\nb","filler":4}
8<-------------------------------

At this point "COPY TO" should be sending exactly the unaltered output
of the postgres JSON processing functions.

--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com