Re: [PATCHES] Some Documentation Changes
Peter Eisentraut wrote: > Am Dienstag, 30. Dezember 2003 03:01 schrieb Christopher Browne: > > 1. In keeping with the recent discussion that there should be more > > said about views, stored procedures, and triggers, in the tutorial, I > > have added a bit of verbiage to that end. > > The idea that seems to get lost here is that the Tutorial is supposed to be > something for people to try out, not just a list of interesting ideas to keep > in mind for later on. > > > 2. Some formatting changes to the datetime discussion, > > Please revert them. > > > as well as > > addition of a citation of a relevant book on calendars. > > Citations go into the bibliography. OK, entire patch reverted. Does someone want to rework this information to fit into our docs more cleanly? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/advanced.sgml === RCS file: /cvsroot/pgsql-server/doc/src/sgml/advanced.sgml,v retrieving revision 1.38 retrieving revision 1.40 diff -c -c -r1.38 -r1.40 *** doc/src/sgml/advanced.sgml 29 Nov 2003 19:51:36 - 1.38 --- doc/src/sgml/advanced.sgml 30 Mar 2004 22:08:50 - 1.40 *** *** 1,5 --- 1,5 *** *** 65,74 Views can be used in almost any place a real table can be used. ! Building views upon other views is not uncommon. - Foreign Keys --- 65,88 Views can be used in almost any place a real table can be used. ! Building views upon other views is not uncommon. You may cut down ! on the difficulty of building complex queries by constructing them ! in smaller, easier-to-verify pieces, using views. Views may be ! used to reveal specific table columns to users that legitimately ! need access to some of the data, but who shouldn't be able to look ! at the whole table. + + Views differ from real tables in that they are + not, by default, updatable. If they join together several tables, + it may be troublesome to update certain columns since the + real update that must take place requires + identifying the relevant rows in the source tables. This is + discussed further in . + + + Foreign Keys *** *** 387,392 --- 401,569 + + Stored Procedures + + + stored procedures + + + Stored procedures are code that runs inside the database +system. Numerous languages may be used to implement functions and +procedures; most built-in code is implemented in C. The +basic loadable procedural language for +PostgreSQL is . +Numerous other languages may also be used, including , , and . + + + There are several ways that stored procedures are really +helpful: + + + + To centralize data validation code into the +database + + Your system may use client software written in several +languages, perhaps with a web application +implemented in PHP, a server application implemented +in Java, and a report writer implemented in Perl. +In the absence of stored procedures, you will likely find that data +validation code must be implemented multiple times, in multiple +languages, once for each application. + + By implementing data validation in stored procedures, +running in the database, it can behave uniformly for all these +systems, and you do not need to worry about synchronizing +validation procedures across the languages. + + + + Reducing round trips between client and server + + +A stored procedure may submit multiple queries, looking up +information and adding in links to additional tables. This takes +place without requiring that the client submit multiple queries, +and without requiring any added network traffic. + + + As a matter of course, the queries share a single +transaction context, and there may also be savings in the +evaluation of query plans, that will be similar between invocations +of a given stored procedure. + + To simplify queries. + + For instance, if you are commonly checking the TLD on domain +names, you might create a stored procedure for this purpose, and so +be able to use queries such as select domain, tld(domain) +from domains; instead of having to put verbose code +using substr() into each query. + + + It is particularly convenient to use scripting languages +like Perl, Tcl, and Python to grovel through strings +since they are designed for text processing. + + The binding to the R statistical language allows +implemen
Re: [PATCHES] Some Documentation Changes
Am Dienstag, 30. März 2004 23:58 schrieb Bruce Momjian: > Patch applied. Thanks. Please revert this patch. The material is not "Tutorial" material. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Some Documentation Changes
Am Dienstag, 30. Dezember 2003 03:01 schrieb Christopher Browne: > 1. In keeping with the recent discussion that there should be more > said about views, stored procedures, and triggers, in the tutorial, I > have added a bit of verbiage to that end. The idea that seems to get lost here is that the Tutorial is supposed to be something for people to try out, not just a list of interesting ideas to keep in mind for later on. > 2. Some formatting changes to the datetime discussion, Please revert them. > as well as > addition of a citation of a relevant book on calendars. Citations go into the bibliography. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Some Documentation Changes
Patch applied. Thanks. --- Christopher Browne wrote: > 1. In keeping with the recent discussion that there should be more > said about views, stored procedures, and triggers, in the tutorial, I > have added a bit of verbiage to that end. > > 2. Some formatting changes to the datetime discussion, as well as > addition of a citation of a relevant book on calendars. > > Index: advanced.sgml > === > RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/advanced.sgml,v > retrieving revision 1.38 > diff -c -u -r1.38 advanced.sgml > --- advanced.sgml 29 Nov 2003 19:51:36 - 1.38 > +++ advanced.sgml 30 Dec 2003 01:58:24 - > @@ -65,10 +65,24 @@ > > > Views can be used in almost any place a real table can be used. > -Building views upon other views is not uncommon. > +Building views upon other views is not uncommon. You may cut down > +on the difficulty of building complex queries by constructing them > +in smaller, easier-to-verify pieces, using views. Views may be > +used to reveal specific table columns to users that legitimately > +need access to some of the data, but who shouldn't be able to look > +at the whole table. > > - > > + > +Views differ from real tables in that they are > +not, by default, updatable. If they join together several tables, > +it may be troublesome to update certain columns since the > +real update that must take place requires > +identifying the relevant rows in the source tables. This is > +discussed further in . > + > + > + > > > Foreign Keys > @@ -387,6 +401,169 @@ > > > > + > +Stored Procedures > + > + > + stored procedures > + > + > +Stored procedures are code that runs inside the database > + system. Numerous languages may be used to implement functions and > + procedures; most built-in code is implemented in C. The > + basic loadable procedural language for > + PostgreSQL is . > + Numerous other languages may also be used, including + linkid="plperl">, , and + linkid="plpython">. > + > + > +There are several ways that stored procedures are really > + helpful: > + > + > + > +To centralize data validation code into the > + database > + > +Your system may use client software written in several > + languages, perhaps with a web application > + implemented in PHP, a server application implemented > + in Java, and a report writer implemented in Perl. > + In the absence of stored procedures, you will likely find that data > + validation code must be implemented multiple times, in multiple > + languages, once for each application. > + > +By implementing data validation in stored procedures, > + running in the database, it can behave uniformly for all these > + systems, and you do not need to worry about synchronizing > + validation procedures across the languages. > + > + > + > +Reducing round trips between client and server > + > + > + A stored procedure may submit multiple queries, looking up > + information and adding in links to additional tables. This takes > + place without requiring that the client submit multiple queries, > + and without requiring any added network traffic. > + > + > +As a matter of course, the queries share a single > + transaction context, and there may also be savings in the > + evaluation of query plans, that will be similar between invocations > + of a given stored procedure. > + > +To simplify queries. > + > +For instance, if you are commonly checking the TLD on domain > + names, you might create a stored procedure for this purpose, and so > + be able to use queries such as select domain, tld(domain) > + from domains; instead of having to put verbose code > + using substr() into each query. > + > + > +It is particularly convenient to use scripting languages > + like Perl, Tcl, and Python to grovel through strings > + since they are designed for text processing. > + > +The binding to the R statistical language allows > + implementing complex statistical queries inside the database, > + instead of having to draw the data out. > + > + > +Increasing the level of abstraction > + > +If data is accessed exclusively through stored procedures, > + then the structures of tables may be changed without there needing > + to be any visible change in the API used by programmers. In some > + systems, users are only allowed access to > + stored procedures to update data, and cannot do direct updates to > + tables. > + > + > + > + > + > + > + > +These benefits build on one another: careful use of stored > + procedures can simultaneously improve reliability and performance, > + whilst simplifying database acc
