Re: Preserving the source code of views

Hello

2013/10/20 Brian Crowell <brian(at)fluggo(dot)com>

> 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;

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
>