[GENERAL] Views - Under the Hood
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... -- Terry Lee Tucker tel: (336) 372-5432; cell: (336) 404-6897 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views - Under the Hood
On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker te...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views - Under the Hood
On Thursday, November 04, 2010 15:03:49 Scott Marlowe wrote: On Thu, Nov 4, 2010 at 12:43 PM, Terry Lee Tucker te...@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 te...@chosen-ones.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views - Under the Hood
te...@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] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views - Under the Hood
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Views - Under the Hood
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 te...@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 == -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
