> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
