Views - Under the Hood

On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker <terry@chosen-ones.org> wrote:

Greetings:

Lately, I've begun using views quite often especially when queries for various
reports, etc. become complicated. I am now wondering if there is a price to
pay in terms of overhead for this. In truth, I don't really understand how a
view works. I know that it takes on many of the attributes of a table, but is
it a table? Is the data pulled together when one selects from the view or is
it maintained as a table all along. Guidance to the ignorant appreciated...

In pgsql a view is actually a rule that fires off the original query
for you. So it's a simple wrapper, and is the same, for the most
part, as simply typing in the original query again. So, it's pretty
simple, and there's no real overhead to worry about.

On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote:

On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker <terry@chosen-ones.org>

wrote:

Greetings:

Lately, I've begun using views quite often especially when queries for
various reports, etc. become complicated. I am now wondering if there is
a price to pay in terms of overhead for this. In truth, I don't really
understand how a view works. I know that it takes on many of the
attributes of a table, but is it a table? Is the data pulled together
when one selects from the view or is it maintained as a table all along.
Guidance to the ignorant appreciated...

In pgsql a view is actually a rule that fires off the original query
for you. So it's a simple wrapper, and is the same, for the most
part, as simply typing in the original query again. So, it's pretty
simple, and there's no real overhead to worry about.

Thank you Scott! This is exactly what I needed to know...

--
Terry Lee Tucker
tel: (336) 372-5432; cell: (336) 404-6897
terry@chosen-ones.org

terry@chosen-ones.org (Terry Lee Tucker) writes:

Lately, I've begun using views quite often especially when queries for various
reports, etc. become complicated. I am now wondering if there is a price to
pay in terms of overhead for this. In truth, I don't really understand how a
view works. I know that it takes on many of the attributes of a table, but is
it a table? Is the data pulled together when one selects from the view or is
it maintained as a table all along. Guidance to the ignorant appreciated...

Under the hood, views represent a rewriting of the query.

http://www.postgresql.org/docs/8.4/static/rules-views.html

If you have two tables that are joined together, in a view, then when
you query the view, you're really running a more complex query than
you're seeing, namely one that joins together the two tables, and does
whatever else you put into your query.

It *looks* like a table, for almost all intents and purposes, but what
it is, really, is a structure that leads to your queries being rewritten
to access the *real* tables that underly the view.

So the date is, as you suggest, "pulled together when one selects from
the view."
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/slony.html
"People are more vocally opposed to fur than leather because it's
easier to harass rich women than motorcycle gangs." [bumper sticker]

One of the benefits of writing views instead of using SQL in your code,
is that any developer or developer tool can use the view.
So the DB developer writes the view and maybe define indexes that can
speed up the query and any developer of any software that uses the DB
can refer to the View instead of writing the SQL.
Moreover if sw developers use tools like ORMs those toolo can take
advantage of the View and write all the code to use them to speed up the
sw developer work.

Il 04/11/2010 20.24, Terry Lee Tucker ha scritto:

On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote:

On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker <terry@chosen-ones.org>

wrote:

Greetings:

Lately, I've begun using views quite often especially when queries for
various reports, etc. become complicated. I am now wondering if there is
a price to pay in terms of overhead for this. In truth, I don't really
understand how a view works. I know that it takes on many of the
attributes of a table, but is it a table? Is the data pulled together
when one selects from the view or is it maintained as a table all along.
Guidance to the ignorant appreciated...

In pgsql a view is actually a rule that fires off the original query
for you. So it's a simple wrapper, and is the same, for the most
part, as simply typing in the original query again. So, it's pretty
simple, and there's no real overhead to worry about.

Thank you Scott! This is exactly what I needed to know...

--
==================================================
dott. Ivano Mario Luberti
Archimede Informatica societa' cooperativa a r. l.
Sede Operativa
Via Gereschi 36 - 56126- Pisa
tel.: +39-050- 580959
tel/fax: +39-050-9711344
web: www.archicoop.it
==================================================

On 04/11/2010 19:58, Chris Browne wrote:

Under the hood, views represent a rewriting of the query.

http://www.postgresql.org/docs/8.4/static/rules-views.html

If you have two tables that are joined together, in a view, then when
you query the view, you're really running a more complex query than
you're seeing, namely one that joins together the two tables, and does
whatever else you put into your query.

It *looks* like a table, for almost all intents and purposes, but what
it is, really, is a structure that leads to your queries being rewritten
to access the *real* tables that underly the view.

Besides not being able to write to views without adding extra rules, are
there are other intents and purposes for which a view doesn't look like
a table?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie