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

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".

regards, tom lane