> 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?
To put it simple: views are basically stored SQL queries. Functions are just
that - functions. The later require parsing most of the time, the former are
simply executed like any other query (I think views are actually kind of
prepared, so less overhead than executing the same query several times, but I
may be wrong and there's far more qualified people on this list to answer that)
I run a website with a few million pages a month and every page is assembled
out of database records (around 200 tables). I use mostly views for pretty
much everything, since a normalized database almost always requires joins over
several tables to get the result you want. The framework I use requires
SQLAlchemy (a python object oriented SQL mapper) - and I'm just not the person
coding SQL in python when I have a perfectly good database much better at that
task. So I use views and only use the mapper to map the result from SQL to
python.
I learned by experience that functions/procedures are slower and in my eyes
more cumbersome to maintain and debug. I maintain all the database source in
a SCM and I don't use any graphical tools for the SQL - just a good old emacs
does it for me nicely. I also have SQL scripts that allow me to update views.
My views often depend on each other, so replacing one ususally cascades to
others breaking the whole scheme. A script applying the views in correct order
helps a lot on that one.
So basically, use views for performance , maintenance and sometimes
programming reasons (views behave like tables and often your application layer
can't tell the difference, which helps) and use functions where you need the
extra functionality which a view simply can't provide (i.e. you need to update
a record when someone views a different record.) Also think about triggers,
they can be quite useful for i.e. my example about needing to update a record.
Triggers don't require you to explicitly call the function - the database will
do that for you (which kind of obscures that there is something happening...)
Uwe
