diff options
Diffstat (limited to 'doc/manual/extend.html')
| -rw-r--r-- | doc/manual/extend.html | 199 |
1 files changed, 199 insertions, 0 deletions
diff --git a/doc/manual/extend.html b/doc/manual/extend.html new file mode 100644 index 00000000000..a3cdfc06211 --- /dev/null +++ b/doc/manual/extend.html @@ -0,0 +1,199 @@ +<HTML> +<HEAD> + <TITLE>The POSTGRES95 User Manual - EXTENDING SQL: AN OVERVIEW</TITLE> +</HEAD> + +<BODY> + +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="advanced.html">[ Previous ]</A> +<A HREF="xfunc.html">[ Next ]</A> +</font> +<HR> + +<H1>6. EXTENDING SQL: AN OVERVIEW</H1> +<HR> + In the sections that follow, we will discuss how you + can extend the POSTGRES <B>SQL</B> query language by adding: + <UL> + <LI>functions + <LI>types + <LI>operators + <LI>aggregates + </UL> +<p> +<H2><A NAME="how-extensibility-works">6.1. How Extensibility Works</A></H2> + POSTGRES is extensible because its operation is + catalog-driven. If you are familiar with standard + relational systems, you know that they store information + about databases, tables, columns, etc., in what are + commonly known as system catalogs. (Some systems call + this the data dictionary). The catalogs appear to the + user as classes, like any other, but the DBMS stores + its internal bookkeeping in them. One key difference + between POSTGRES and standard relational systems is + that POSTGRES stores much more information in its + catalogs -- not only information about tables and columns, + but also information about its types, functions, access + methods, and so on. These classes can be modified by + the user, and since POSTGRES bases its internal operation + on these classes, this means that POSTGRES can be + extended by users. By comparison, conventional + database systems can only be extended by changing hardcoded + procedures within the DBMS or by loading modules + specially-written by the DBMS vendor. + POSTGRES is also unlike most other data managers in + that the server can incorporate user-written code into + itself through dynamic loading. That is, the user can + specify an object code file (e.g., a compiled .o file + or shared library) that implements a new type or function + and POSTGRES will load it as required. Code written + in <B>SQL</B> are even more trivial to add to the server. + This ability to modify its operation "on the fly" makes + POSTGRES uniquely suited for rapid prototyping of new + applications and storage structures. + +<H2><A NAME="the-postgres-type-system">6.2. The POSTGRES Type System</A></H2> + The POSTGRES type system can be broken down in several + ways. + Types are divided into base types and composite types. + Base types are those, like <CODE>int4</CODE>, that are implemented + in a language such as <B>C</B>. They generally correspond to + what are often known as "abstract data types"; POSTGRES + can only operate on such types through methods provided + by the user and only understands the behavior of such + types to the extent that the user describes them. + Composite types are created whenever the user creates a + class. EMP is an example of a composite type. + POSTGRES stores these types in only one way (within the + file that stores all instances of the class) but the + user can "look inside" at the attributes of these types + from the query language and optimize their retrieval by + (for example) defining indices on the attributes. + POSTGRES base types are further divided into built-in + types and user-defined types. Built-in types (like + <CODE>int4</CODE>) are those that are compiled into the system. + User-defined types are those created by the user in the + manner to be described below. + +<H2><A NAME="about-the-postgres-system-catalogs">6.3. About the POSTGRES System Catalogs</A></H2> + Having introduced the basic extensibility concepts, we + can now take a look at how the catalogs are actually + laid out. You can skip this section for now, but some + later sections will be incomprehensible without the + information given here, so mark this page for later + reference. + All system catalogs have names that begin with <CODE>pg_</CODE>. + The following classes contain information that may be + useful to the end user. (There are many other system + catalogs, but there should rarely be a reason to query + them directly.) + <p> +<center> +<table border=1> +<tr> + <th>catalog name</th><th> description </th> +</tr> +<tr> + <td><CODE>pg_database</CODE> </td><td> databases </td> +</tr> +<tr> + <td><CODE>pg_class</CODE> </td><td> classes </td> +</tr> +<tr> + <td><CODE>pg_attribute</CODE> </td><td> class attributes </td> + </tr> +<tr> + <td><CODE>pg_index</CODE> </td><td> secondary indices </td> +</tr> +<tr> +</tr> +<tr> + <td><CODE>pg_proc</CODE> </td><td> procedures (both C and SQL) </td> +</tr> +<tr> + <td><CODE>pg_type</CODE> </td><td> types (both base and complex) </td> +</tr> +<tr> + <td><CODE>pg_operator</CODE> </td><td> operators </td> +</tr> +<tr> + <td><CODE>pg_aggregate</CODE> </td><td> aggregates and aggregate functions </td> +</tr> +<tr> +</tr> +<tr> +</tr> +<tr> + <td><CODE>pg_am</CODE> </td><td> access methods </td> +</tr> +<tr> + <td><CODE>pg_amop</CODE> </td><td> access method operators </td> +</tr> +<tr> + <td><CODE>pg_amproc</CODE> </td><td> access method support functions </td> +</tr> +<tr> + <td><CODE>pg_opclass</CODE> </td><td> access method operator classes </td> +</tr> +</table> +</center> + +<p> + <IMG SRC="figure03.gif" + ALT="Figure 3. The major POSTGRES system catalogs"> + The Reference Manual gives a more detailed explanation + of these catalogs and their attributes. However, Figure 3 + shows the major entities and their relationships + in the system catalogs. (Attributes that do not refer + to other entities are not shown unless they are part of + a primary key.) + This diagram is more or less incomprehensible until you + actually start looking at the contents of the catalogs + and see how they relate to each other. For now, the + main things to take away from this diagram are as follows: + + <OL> + <LI> In several of the sections that follow, we will + present various join queries on the system + catalogs that display information we need to extend + the system. Looking at this diagram should make + some of these join queries (which are often + three- or four-way joins) more understandable, + because you will be able to see that the + attributes used in the queries form foreign keys + in other classes. + <LI> Many different features (classes, attributes, + functions, types, access methods, etc.) are + tightly integrated in this schema. A simple + create command may modify many of these catalogs. + <LI> Types and procedures <A HREF="#6"><font size=-1>[6]</font></A> + are central to the schema. + Nearly every catalog contains some reference to + instances in one or both of these classes. For + example, POSTGRES frequently uses type + signatures (e.g., of functions and operators) to + identify unique instances of other catalogs. + <LI> There are many attributes and relationships that + have obvious meanings, but there are many + (particularly those that have to do with access + methods) that do not. The relationships between + <CODE>pg_am, pg_amop, pg_amproc, pg_operator</CODE> and + <CODE>pg_opclass</CODE> are particularly hard to understand + and will be described in depth (in the section + on interfacing types and operators to indices) + after we have discussed basic extensions. +</OL> +<p> +<HR> +<A NAME="6"><B>6.</B></A> We use the words <I>procedure</I> and <I>function</I> more or less +interchangably. +<HR> +<font size=-1> +<A HREF="pg95user.html">[ TOC ]</A> +<A HREF="advanced.html">[ Previous ]</A> +<A HREF="xfunc.html">[ Next ]</A> +</font> +</BODY> +</HTML> |