Re: Views versus user-defined functions: formatting, comments, performance, etc.

On Sun, Aug 19, 2012 at 8:14 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com> wrote:
>> For various reasons, this often goes the wrong way. Views are often
>> the right way to go. +1 on your comment above -- the right way to do
>> views (and SQL in general) is to organize scripts and to try and avoid
>> managing everything through GUI tools. It works.
>
> The drawback of this approach is that in some cases we need a
> factory function(s) (in terms of the OOP) which returns one or a
> set of objects (i.e. the function returns the view type). But since
> the views are not in the dump we are forced to abandon this solution
> and go with workarounds (such as creating extra composite types
> to use as returning values or use the tables).

Could you elaborate on this?

> PS. I'm tried to found a SQL formatter for format views definitions
> stored in the database, but unsuccessful.

Even if you could find one, I wouldn't use it: the database changes
the structure of you query. Suppose you had:
CREATE VIEW v AS SELECT *, a*b AS product FROM foo;

The database converts that to:
CREATE VIEW v AS SELECT a,b, a*b AS product FROM foo;

That means that if you add columns after the fact, the view definition
in the database will diverge from what the source would create.
Besides that, there are a number of other things that the database
does like add unnecessary casts, column aliases and parentheses that
make a purely textual solution impossible.

merlin