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
