From aaad011940d181469e1d5cf4655105319f6b085c Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 30 Mar 2004 21:58:20 +0000 Subject: [PATCH] 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. Christopher Browne --- doc/src/sgml/advanced.sgml | 183 ++++++++++++++++++++++++++++++++++++- doc/src/sgml/datetime.sgml | 57 ++++++------ 2 files changed, 211 insertions(+), 29 deletions(-) diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 2d7b1d1344..892dfaf3ae 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -1,5 +1,5 @@ @@ -65,10 +65,24 @@ SELECT * FROM myview; 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 @@ SELECT name, altitude + + 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 + 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 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. + + 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. + + + + + Triggers + + + triggers + + + Triggers allow running a function either before or after + update (INSERT, DELETE, + UPDATE) operations, which can allow you to do + some very clever things. + + + + Data Validation + + Instead of explicitly coding validation checks as part of a + stored procedure, they may be introduced as BEFORE + triggers. The trigger function checks the input values, raising an + exception if it finds invalid input. + + Note that this is how foreign key checks are implemented in + PostgreSQL; when you define a foreign + key, you will see a message similar to the following: + +NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) + + + In some cases, it may be appropriate for a trigger function + to insert data in order to make the input valid. For + instance, if a newly created object needs a status code in a status + table, the trigger might automatically do that. + + + Audit logs + + One may use AFTER triggers to monitor updates to + vital tables, and INSERT entries into log tables to + provide a more permanent record of those updates. + + + Replication + + The RServ replication system uses + AFTER triggers to track which rows have changed on the + master system and therefore need to be copied over to + slave systems. + + + CREATE TRIGGER "_rserv_trigger_t_" AFTER INSERT OR DELETE OR UPDATE ON "my_table" + FOR EACH ROW EXECUTE PROCEDURE "_rserv_log_" ('10'); + + + + + + Notice that there are strong parallels between what can be + accomplished using triggers and stored procedures, particularly in + regards to data validation. + + Conclusion diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml index d1f82c9d6e..026fb5d553 100644 --- a/doc/src/sgml/datetime.sgml +++ b/doc/src/sgml/datetime.sgml @@ -1,5 +1,5 @@ @@ -11,8 +11,8 @@ $PostgreSQL: pgsql/doc/src/sgml/datetime.sgml,v 2.39 2003/12/01 20:34:53 tgl Exp 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, + value, ignored, or rejected. + The parser contains internal lookup tables for all textual fields, including months, days of the week, and time zones. @@ -1056,21 +1056,21 @@ $PostgreSQL: pgsql/doc/src/sgml/datetime.sgml,v 2.39 2003/12/01 20:34:53 tgl Exp years. - - 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 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. + 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 cal program - produce the following: + This is why Unix systems have the cal + program produce the following: $ cal 9 1752 @@ -1094,19 +1094,24 @@ $ cal 9 1752 - - Different calendars have been developed in various parts of the - world, many predating the Gregorian system. + 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. + 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. + The People's Republic of China uses the Gregorian calendar + for civil purposes. The Chinese calendar is used for determining + festivals. + + If you are interested in this sort of thing, + Calendrical Calculations: The Millennium Edition 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. + -- 2.40.0