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

On Fri, Aug 17, 2012 at 5:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Adam Mackler <adammackler@gmail.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.

merlin