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:
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.
.
