From: | Joe Abbate <jma(at)freedomcircle(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Spaces before newlines in view definitions in 9.3 |
Date: | 2013-09-10 21:21:58 |
Message-ID: | 522F8D76.8010400@freedomcircle.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Example test code:
$ psql pyrseas_testdb
psql (9.3.0)
Type "help" for help.
pyrseas_testdb=# create table t1 (c1 int, c2 text);
CREATE TABLE
pyrseas_testdb=# create view v1 as select * from t1;
CREATE VIEW
pyrseas_testdb=# \d+ v1
View "public.v1"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+----------+-------------
c1 | integer | | plain |
c2 | text | | extended |
View definition:
SELECT t1.c1,
t1.c2
FROM t1;
It may not be immediately obvious but there is a space after the
"t1.c1," and before the first newline. In 9.2 and previous releases,
the view definition is:
SELECT t1.c1, t1.c2
FROM t1;
If there are more columns, there's an extra space for each except the
last one, e.g., (with _ denoting a trailing space):
SELECT t2.c1,_
t2.c2,_
t2.c3,_
t2.c4
FROM t2;
The problem is that the string comes back, e.g., from pg_get_viewdef()
with those extra spaces before the newlines, e.g.,
" SELECT t1.c1, \n t1.c3 * 2 AS mc3\n FROM t1;
and YAML has a way displaying a text string nicely so that it can be
recovered when it's read back, but it *doesn't* work if there are
invisible characters such as tabs or spaces before a newline because
obviously one can't tell how many or of what kind they are.
Note: This applies to both views and materialized views.
I believe the reformatting of view text (breaking each column on a
separate line) was done to improve readability but it has the side
effect of making the text unreadable if processed via a YAML utility
such as Pyrseas dbtoyaml (since YAML then quotes the entire string and
even breaks it down further with extra backslashes).
Regards,
Joe
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2013-09-10 23:04:14 | Re: BUG #7844: xpath missing entity decoding - bug or feature |
Previous Message | Euler Taveira | 2013-09-10 21:08:19 | Re: BUG #8442: Postgresql Crash Frequently and It is installed on m68k platorm |