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 -0000 1.38 --- doc/src/sgml/advanced.sgml 30 Mar 2004 22:08:50 -0000 1.40 *************** *** 1,5 **** <!-- ! $PostgreSQL: pgsql-server/doc/src/sgml/advanced.sgml,v 1.38 2003/11/29 19:51:36 pgsql Exp $ --> <chapter id="tutorial-advanced"> --- 1,5 ---- <!-- ! $PostgreSQL: pgsql-server/doc/src/sgml/advanced.sgml,v 1.40 2004/03/30 22:08:50 momjian Exp $ --> <chapter id="tutorial-advanced"> *************** *** 65,74 **** <para> Views can be used in almost any place a real table can be used. ! Building views upon other views is not uncommon. </para> - </sect1> <sect1 id="tutorial-fk"> <title>Foreign Keys</title> --- 65,88 ---- <para> 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. </para> + <para> + Views differ from <quote> real tables </quote> in that they are + not, by default, updatable. If they join together several tables, + it may be troublesome to update certain columns since the + <emphasis>real</emphasis> update that must take place requires + identifying the relevant rows in the source tables. This is + discussed further in <xref linkend="rules-views-update">. + </para> + + </sect1> <sect1 id="tutorial-fk"> <title>Foreign Keys</title> *************** *** 387,392 **** --- 401,569 ---- </para> </sect1> + <sect1 id="tutorial-storedprocs"> + <title> Stored Procedures </title> + + <indexterm zone="tutorial-storedprocs"> + <primary>stored procedures</primary> + </indexterm> + + <para> 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 + <quote>basic</quote> loadable procedural language for + <productname>PostgreSQL</productname> is <xref linkend="plpgsql">. + Numerous other languages may also be used, including <xref + linkend="plperl">, <xref linkend="pltcl">, and <xref + linkend="plpython">. + </para> + + <para> There are several ways that stored procedures are really + helpful: + + <itemizedlist> + + <listitem><para> To centralize data validation code into the + database </para> + + <para> Your system may use client software written in several + languages, perhaps with a <quote>web application</quote> + implemented in PHP, a <quote>server application</quote> implemented + in Java, and a <quote> report writer</quote> 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.</para> + + <para> 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.</para> + + </listitem> + + <listitem><para> Reducing round trips between client and server + </para> + + <para>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. + </para> + + <para> 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. </para></listitem> + + <listitem><para> To simplify queries. </para> + + <para> 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 <command> select domain, tld(domain) + from domains; </command> instead of having to put verbose code + using <function>substr()</function> into each query. + </para> + + <para> It is particularly convenient to use scripting languages + like Perl, Tcl, and Python to <quote>grovel through strings</quote> + since they are designed for <quote>text processing.</quote></para> + + <para> The binding to the R statistical language allows + implementing complex statistical queries inside the database, + instead of having to draw the data out. + </listitem> + + <listitem><para> Increasing the level of abstraction</para> + + <para> 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 <emphasis>only</emphasis> allowed access to + stored procedures to update data, and cannot do direct updates to + tables. + </para> + + </listitem> + + </itemizedlist> + </para> + + <para> These benefits build on one another: careful use of stored + procedures can simultaneously improve reliability and performance, + whilst simplifying database access code and improving portability + across client platforms and languages. For instance, consider that + a stored procedure can cheaply query tables in the database to + validate the correctness of data provided as input. </para> + + <para> Instead of requiring a whole series of queries to create an + object, and to look up parent/subsidiary objects to link it to, a + stored procedure can do all of this efficiently in the database + server, improving performance, and eliminating whole classes of + errors. </para> + + </sect1> + + <sect1 id="tutorial-triggers"> + <title> Triggers </title> + + <indexterm zone="tutorial-triggers"> + <primary>triggers</primary> + </indexterm> + + <para> Triggers allow running a function either before or after + update (<command>INSERT</command>, <command>DELETE</command>, + <command>UPDATE</command>) operations, which can allow you to do + some very clever things. </para> + + <itemizedlist> + + <listitem><para> Data Validation </para> + + <para> Instead of explicitly coding validation checks as part of a + stored procedure, they may be introduced as <command>BEFORE</command> + triggers. The trigger function checks the input values, raising an + exception if it finds invalid input.</para> + + <para> Note that this is how foreign key checks are implemented in + <productname>PostgreSQL</productname>; when you define a foreign + key, you will see a message similar to the following: + <screen> + NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + </screen></para> + + <para> In some cases, it may be appropriate for a trigger function + to insert data in order to <emphasis>make</emphasis> the input valid. For + instance, if a newly created object needs a status code in a status + table, the trigger might automatically do that.</para> + </listitem> + + <listitem><para> Audit logs </para> + + <para> One may use <command>AFTER</command> triggers to monitor updates to + vital tables, and <command>INSERT</command> entries into log tables to + provide a more permanent record of those updates. </para> + </listitem> + + <listitem><para> Replication </para> + + <para> The <application>RServ</application> replication system uses + <command>AFTER</command> triggers to track which rows have changed on the + <quote>master</quote> system and therefore need to be copied over to + <quote>slave</quote> systems.</para> + + <para> <command> + CREATE TRIGGER "_rserv_trigger_t_" AFTER INSERT OR DELETE OR UPDATE ON "my_table" + FOR EACH ROW EXECUTE PROCEDURE "_rserv_log_" ('10'); + </command></para> + </listitem> + + </itemizedlist> + + <para> Notice that there are strong parallels between what can be + accomplished using triggers and stored procedures, particularly in + regards to data validation. </para> + + </sect1> <sect1 id="tutorial-conclusion"> <title>Conclusion</title> Index: doc/src/sgml/datetime.sgml =================================================================== RCS file: /cvsroot/pgsql-server/doc/src/sgml/datetime.sgml,v retrieving revision 2.39 retrieving revision 2.40 diff -c -c -r2.39 -r2.40 *** doc/src/sgml/datetime.sgml 1 Dec 2003 20:34:53 -0000 2.39 --- doc/src/sgml/datetime.sgml 30 Mar 2004 21:58:20 -0000 2.40 *************** *** 1,5 **** <!-- ! $PostgreSQL: pgsql-server/doc/src/sgml/datetime.sgml,v 2.39 2003/12/01 20:34:53 tgl Exp $ --> <appendix id="datetime-appendix"> --- 1,5 ---- <!-- ! $PostgreSQL: pgsql-server/doc/src/sgml/datetime.sgml,v 2.40 2004/03/30 21:58:20 momjian Exp $ --> <appendix id="datetime-appendix"> *************** *** 11,18 **** strings, and are broken up into distinct fields with a preliminary determination of what kind of information may be in the field. Each field is interpreted and either assigned a numeric ! value, ignored, or rejected. ! The parser contains internal lookup tables for all textual fields, including months, days of the week, and time zones. </para> --- 11,18 ---- strings, and are broken up into distinct fields with a preliminary determination of what kind of information may be in the field. Each field is interpreted and either assigned a numeric ! value, ignored, or rejected.</para> ! <para> The parser contains internal lookup tables for all textual fields, including months, days of the week, and time zones. </para> *************** *** 1056,1076 **** years. </para> ! <para> ! The papal bull of February 1582 decreed that 10 days should be dropped ! from October 1582 so that 15 October should follow immediately after ! 4 October. ! This was observed in Italy, Poland, Portugal, and Spain. Other Catholic ! countries followed shortly after, but Protestant countries were ! reluctant to change, and the Greek orthodox countries didn't change ! until the start of the 20th century. ! ! The reform was observed by Great Britain and Dominions (including what is ! now the USA) in 1752. ! Thus 2 September 1752 was followed by 14 September 1752. ! This is why Unix systems have the <command>cal</command> program ! produce the following: <screen> $ <userinput>cal 9 1752</userinput> --- 1056,1076 ---- years. </para> ! <para> The papal bull of February 1582 decreed that 10 days should ! be dropped from October 1582 so that 15 October should follow ! immediately after 4 October.</para> ! ! <para> This was observed in Italy, Poland, Portugal, and Spain. ! Other Catholic countries followed shortly after, but Protestant ! countries were reluctant to change, and the Greek orthodox countries ! didn't change until the start of the 20th century.</para> ! ! <para> The reform was observed by Great Britain and Dominions ! (including what is now the USA) in 1752. Thus 2 September 1752 was ! followed by 14 September 1752.</para> ! <para> This is why Unix systems have the <command>cal</command> ! program produce the following: <screen> $ <userinput>cal 9 1752</userinput> *************** *** 1094,1112 **** </para> </note> ! <para> ! Different calendars have been developed in various parts of the ! world, many predating the Gregorian system. ! For example, ! the beginnings of the Chinese calendar can be traced back to the 14th ! century BC. Legend has it that the Emperor Huangdi invented the ! calendar in 2637 BC. ! The People's Republic of China uses the Gregorian calendar ! for civil purposes. The Chinese calendar is used for determining ! festivals. </para> </sect1> </appendix> --- 1094,1117 ---- </para> </note> ! <para> Different calendars have been developed in various parts of ! the world, many predating the Gregorian system.</para> ! <para> For example, the beginnings of the Chinese calendar can be ! traced back to the 14th century BC. Legend has it that the Emperor ! Huangdi invented the calendar in 2637 BC.</para> ! <para> The People's Republic of China uses the Gregorian calendar ! for civil purposes. The Chinese calendar is used for determining ! festivals. </para> + + <para> If you are interested in this sort of thing, <citation> + Calendrical Calculations: The Millennium Edition </citation> by by + Edward M. Reingold and Nachum Dershowitz is an excellent reference, + describing some 25 calendars, and providing software for displaying + them and converting between them.</para> + </sect1> </appendix>
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])