On Fri, Aug 17, 2012 at 2:35 PM, Adam Mackler
<adammackler@gmail.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
