Re: Preserving the source code of views

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: Preserving the source code of views
Дата
Msg-id CAFj8pRCY0Z=s_SbC8W6Bbei3GRn4dJJ+ahjH_4-tSgrbAkbUkQ@mail.gmail.com
обсуждение исходный текст
Ответ на Preserving the source code of views  (Brian Crowell <brian@fluggo.com>)
Ответы Re: Preserving the source code of views  (Brian Crowell <brian@fluggo.com>)
Список pgsql-general
Дерево обсуждения
Hello


2013/10/20 Brian Crowell <brian@fluggo.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

В списке pgsql-general по дате отправления:

Предыдущее
От: Gavin Flower
Дата:
Сообщение: Re: Preserving the source code of views
Следующее
От: "Huang, Suya"
Дата:
Сообщение: Re: [ADMIN] what's the efficient/safest way to convert database character set ?