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

2012/8/18 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Aug 17, 2012 at 5:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Adam Mackler <adammackler(at)gmail(dot)com> writes:
> >> I notice when I save a view, I lose all the formatting and comments.
> >> As I was writing a complicated view, wanting to retain the format and
> >> comments, I thought I could just save it as a function that returns a
> >> table value. A function would evaluate to the same value as a view,
> >> but changing it later might be less confusing.
> >
> > A lot of people choose to save the source text in some sort of SCM
> > (eg git), and then just import via CREATE OR REPLACE VIEW when they
> > change it. This tends to soon scale up to large scripts that define
> > collections of related objects.
> >
> >> However, I'm guessing (since I don't know anything about the
> >> internals) that the loss of formatting and comments is a result of the
> >> view being processed and stored in a more computer-friendly format,
> >> while functions are simply stored as the text that I type.
> >
> > Correct. The reparse time per se is generally not a big deal, but the
> > execution penalty associated with a function can be. If you go this way
> > you'll want to make sure that your function can be "inlined" --- use
> > EXPLAIN to make sure you get a plan matching the bare view, and not just
> > something that says "Function Scan".
>
> 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).

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

--
// Dmitriy.