Hello
2013/10/20 Brian Crowell <brian@xxxxxxxxxx>
postgres=# create view simply as select 10;
CREATE VIEW
postgres=# comment on view simply is 'very simple view';
COMMENT
postgres=# \dv+
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
────────┼────────┼──────┼───────┼─────────┼──────────────────
public │ simply │ view │ pavel │ 0 bytes │ very simple view
(1 row)
Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our in-house systems. I've been really impressed with it so far, and I'm eager to try it with our data sets.I've run across one thing that would make a transfer difficult. Postgres doesn't preserve the source code for views, as far as I can tell. It parses them and then prints them its own way. We have a lot of complicated views, where both the formatting and the comments are significant. In fact, we produce some of our system documentation directly from the comments.Is there currently a way to preserve the original source code of a view as entered in the CREATE VIEW statement?
I don't known about any way, how to do it (without hacking postgresql source code). PostgreSQL saves a views in preprocessed form from performance reasons.
There are a few recommendation how to solve this issue - I never had a problem with it, because I use a different workflow.
a) never modify a database object structure in database with admin tools. Use a SQL scripts ever.
* a admin tools has not good has not good editors
* there are no possibility to join related code together
* there are no good versioning
* a portability of handly written SQL scripts is significantly better than SQL scripts generated by admin tools
I ever write a SQL scripts saved in files - then I can to push on one place (one file) related different objects - triggers, views, tables, procedures - with comments on file start, and with comments before any object.
b) if you don't like @a, use a COMMENTs
postgres=# create view simply as select 10;
CREATE VIEW
postgres=# comment on view simply is 'very simple view';
COMMENT
postgres=# \dv+
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
────────┼────────┼──────┼───────┼─────────┼──────────────────
public │ simply │ view │ pavel │ 0 bytes │ very simple view
(1 row)
a 9.2 and newer PostgreSQL formats a materialized view - so it lost your formatting, but result will be in good format too.
postgres=# create view simply as select 10 from pg_class where true;
CREATE VIEW
Time: 97.584 ms
postgres=# \d+ simply
View "public.simply"
Column │ Type │ Modifiers │ Storage │ Description
──────────┼─────────┼───────────┼─────────┼─────────────
?column? │ integer │ │ plain │
View definition:
SELECT 10
FROM pg_class
WHERE true;
postgres=# create view simply as select 10 from pg_class where true;
CREATE VIEW
Time: 97.584 ms
postgres=# \d+ simply
View "public.simply"
Column │ Type │ Modifiers │ Storage │ Description
──────────┼─────────┼───────────┼─────────┼─────────────
?column? │ integer │ │ plain │
View definition:
SELECT 10
FROM pg_class
WHERE true;
For my work is very significant @a point - I wrote and I am writing usually database centric stored procedures centric applications and @a works perfect. For me a SQL code is code as any other - I use a my favourite editor, I use a GIT for versioning, I can simple distributed application to my customers.
Regards
Pavel
--Brian
