Re: Proposal to improve the content in subsection 8.16.6. "CompositeType Input and Output Syntax" - Mailing list pgsql-docs

Tom Lane <tgl@sss.pgh.pa.us> wrote:

...

It's already an example, so I don't see this as an improvement.

...

The proposed additional text is flat-out wrong.

What actually happens here is that text between quotes is considered
quoted (so that, for example, commas within it are not field separators),
but that does not exclude there being other unquoted text within the
same field value.

regards, tom lane

Thanks, Tom. Your reply was very helpful. It prompted me to read, re-read, and then re-re-read 8.16.6 several times. I did notice that neither of “varchar” nor “char” are found in the whole of section 8.16

But I suppose that it’s obvious to the reader that “text” is to be taken as a term of art, denoting “character data types” and not as the name of one of these data types.

Second: for this specified target:

> hello "you" <

I found that each of these six literals produced the same result:

( hello \"you\” )
("" hello \"you\" "")
(" hello \"you\" ")
(" hello ""you"" ")
( "hello \"you\"" )
( "hello ""you""" )

The result was canonically rendered in psql thus:

(" hello ""you"" ")

This observation tells me that this:

""

serves as the syntax to escape a single double quote within a surrounding double quote pair, as an alternative to this:

\"

I searched in the whole of section 8.16 for this:

""

but I found its meaning defined only as the empty string when it occurs outside of a surrounding double quote pair. I assume that it’s obvious to most readers that it has a second meaning in the other context.

I found that, with is extra one rule, I could explain all my observations. 

I observed, over all my tests, that there always exists a canonical form like this:

("...")

where the ellipsis indicates a well-formed sequence of characters that produces my specified target.

I’m going to elevate this to a hypothesis.

And unless/until it’s disproved, I’m going to adopt a rule of practice (for myself) always to use this form.

literal-for-array-of-records.sql

--------------------------------

This was designed to help me meet my ultimate goal: I need to write a client program (say, in python) that will process data from a file whose semantic content is sufficient to let me populate the “rt[]” field in one row of a table, given by “type rt as (n numeric, s text, t timestamp, b booleaan)”.

I’m afraid I couldn’t find the rules in the Version 11 PG doc. This probably reflects my poor searching skills.

   n   |          s          |          t          | b 
-------+---------------------+---------------------+---
 19.13 | "Hello", she\he\r  +| 2019-07-21 16:47:00 | f
       | (said | is saying). |                     | 

The “\r”, along with the “+” that I’m used to, seem to be artifacts of how psql displays what I created with chr(10). But only in this special context. (This "select 'a'||chr(10)||’b'” produces only the “+” but not the “\r”. Strange.)

I discovered that the output is produced by this:

{

  "(42.17,dog,\"2020-02-17 20:55:00\",t)",
  "(19.13,\"\"\"Hello\"\", she\\\\he
(said | is saying).\",\"2019-07-21 16:47:00\",f)"

}

I am able to formulate the rules to produce this programmatically (in Python) starting with my target values expressed as Python variables. Here’s how I did it. Notice, that for simplicity here, “rt” has just a single text field. The proper code has to put the surrounding double quootes and parentheses around the comma-separated list of escaped field representations.