I’m using PostgreSQL Version 11.2. Try this:
create type rt as (a text, b text);
create table t(k serial primary key, r rt);
insert into t(r) values
('("a","b")'),
('( "e" , "f" )'),
('( "g (h)" , "i, j" )');
select
k,
'>'||(r).a||'<' as a,
'>'||(r).b||'<' as b
from t order by k;
This is the result.
k | a | b
---+-----------+----------
1 | >a< | >b<
2 | > e < | > f <
3 | > g (h) < | > i, j <
The documentation in section “8.16.2. Constructing Composite Values” here:
https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-IO-SYNTAX
<https://www.postgresql.org/docs/11/rowtypes.html#ROWTYPES-IO-SYNTAX>
shows examples of using double quotes to surround a text value inside the
literal for a user-defined record type—and it states that this is optional
unless the text value contains a comma or a parenthesis. But in every example
there is no case where the syntax elements (the surrounding parentheses and the
comma separators) outside of the values themselves have space(s) on either
side. So it gives no basis to explain the result that I show for “k=2” and
“k=3”.
Intuition tells me that if a text value is surrounded by double quotes, then
these delimit the string and that anything outside of this (baring the special
case of a text value that is *not* surrounded by double quotes), then
whitespace can be used—as it is in every other case that I can think of in
PostgreSQL’s SQL and PL/pgSQL, at the programers discretion to improve
readability.
This, by the way, is the rule for a JSON string value.
In fact, the rule seems to be this:
“When a text value is written inside the literal for a user-defined type (which
data type is given by its declaration), the entire run of characters between
the syntax elements—the opening left parenthesis, an interior comma, or the
closing right parenthesis— is taken to be the text value, including spaces
outside of the quotes.”
Have I stumbled on a bug? If not, please explain the rationale for what seems
to me to be a counter-intuitive syntax design choice.
.
