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

On Fri, Aug 17, 2012 at 2:35 PM, Adam Mackler <adammackler(at)gmail(dot)com> wrote:

> Hi:
>
> 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.
>
> 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. That gives
> me reason to suspect there may be performance or other differences
> between the same SQL statement stored either as a view or a
> user-defined function.
>
> So that's my question: as someone who doesn't have a problem with
> putting a pair of empty parentheses at the end of a table variable
> name, what factors should I be thinking of while deciding whether to
> store my self-composed, multi-hundred-line long SQL statement as a
> view or a function?
>
> As someone who does a lot of both, here's my take.

Functions are often relatively opaque planner-wise. Some SQL-language
functions can be inlined. Not all can be. I usually assume that a
function which is hitting a table cannot be inlined. This is a useful
assumption even though it is wrong in some cases. Also views and functions
create interfaces for your data, and both can be unit-tested (which is
something that should not be estimated in importance).

In general if I want a re-usable quasi-relation, I create a view. If I
want a data interface, I create a function. If I want a data
transformation interface, then a function is the right tool.

Because of the work I do I create a lot more functions than views, but both
have their uses,. Also I would second Tom's suggestion regarding use of an
external source code management solution. It's not that hard to create
shell scripts that reload a set of db scripts, and this way you can use
your scm versions as authoritative. However with these be aware of what
happens when you make certain changes. For example it isn't too hard to
unintentionally end up with overloaded functions, or have a view refuse to
be rebuilt because of some changes made. These require some knowledge of
what's safe to in your specific application.

Best Wishes,
Chris Travers