diff options
Diffstat (limited to 'doc/src/sgml/extend.sgml')
| -rw-r--r-- | doc/src/sgml/extend.sgml | 250 |
1 files changed, 250 insertions, 0 deletions
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml new file mode 100644 index 0000000000..fe790b2542 --- /dev/null +++ b/doc/src/sgml/extend.sgml @@ -0,0 +1,250 @@ +<Chapter> +<Title>Extending <Acronym>SQL</Acronym>: An Overview</Title> + +<Para> + In the sections that follow, we will discuss how you + can extend the <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym> query language by adding: +<ItemizedList Mark="bullet" Spacing="compact"> +<ListItem> +<Para> + functions +</Para> +</ListItem> +<ListItem> +<Para> + types +</Para> +</ListItem> +<ListItem> +<Para> + operators +</Para> +</ListItem> +<ListItem> +<Para> + aggregates +</Para> +</ListItem> +</ItemizedList> +</Para> + +<Sect1> +<Title>How Extensibility Works</Title> + +<Para> + <ProductName>Postgres</ProductName> 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 <Acronym>DBMS</Acronym> stores + its internal bookkeeping in them. One key difference + between <ProductName>Postgres</ProductName> and standard relational systems is + that <ProductName>Postgres</ProductName> 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 <ProductName>Postgres</ProductName> bases its internal operation + on these classes, this means that <ProductName>Postgres</ProductName> can be + extended by users. By comparison, conventional + database systems can only be extended by changing hardcoded + procedures within the <Acronym>DBMS</Acronym> or by loading modules + specially-written by the <Acronym>DBMS</Acronym> vendor. +</Para> +<Para> + <ProductName>Postgres</ProductName> 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 <ProductName>Postgres</ProductName> will load it as required. Code written + in <Acronym>SQL</Acronym> are even more trivial to add to the server. + This ability to modify its operation "on the fly" makes + <ProductName>Postgres</ProductName> uniquely suited for rapid prototyping of new + applications and storage structures. +</Para> +</Sect1> + +<Sect1> +<Title>The <ProductName>Postgres</ProductName> Type System</Title> + +<Para> + The <ProductName>Postgres</ProductName> type system can be broken down in several ways. + Types are divided into base types and composite types. + Base types are those, like <FirstTerm>int4</FirstTerm>, that are implemented + in a language such as <ProductName>C</ProductName>. They generally correspond to + what are often known as "abstract data types"; <ProductName>Postgres</ProductName> + 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. +</Para> +<Para> + <ProductName>Postgres</ProductName> 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. + <ProductName>Postgres</ProductName> base types are further divided into built-in + types and user-defined types. Built-in types (like + <FirstTerm>int4</FirstTerm>) are those that are compiled into the system. + User-defined types are those created by the user in the + manner to be described below. +</Para> +</Sect1> + +<Sect1> +<Title>About the <ProductName>Postgres</ProductName> System Catalogs</Title> + +<Para> + 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 <FirstTerm>pg_</FirstTerm>. + 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.) + +<TABLE TOCENTRY="1"> +<TITLE>Postgres System Catalogs</TITLE> +<TITLEABBREV>Catalogs</TITLEABBREV> +<TGROUP COLS="2"> +<THEAD> +<ROW> +<ENTRY>Catalog Name</ENTRY> +<ENTRY>Description</ENTRY> +</ROW> +</THEAD> +<TBODY> +<ROW> +<ENTRY>pg_database</ENTRY> +<ENTRY> databases</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_class</ENTRY> +<ENTRY> classes</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_attribute</ENTRY> +<ENTRY> class attributes</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_index</ENTRY> +<ENTRY> secondary indices</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_proc</ENTRY> +<ENTRY> procedures (both C and SQL)</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_type</ENTRY> +<ENTRY> types (both base and complex)</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_operator</ENTRY> +<ENTRY> operators</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_aggregate</ENTRY> +<ENTRY> aggregates and aggregate functions</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_am</ENTRY> +<ENTRY> access methods</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_amop</ENTRY> +<ENTRY> access method operators</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_amproc</ENTRY> +<ENTRY> access method support functions</ENTRY> +</ROW> +<ROW> +<ENTRY>pg_opclass</ENTRY> +<ENTRY> access method operator classes</ENTRY> +</ROW> +</TBODY> +</TGROUP> +</TABLE> +</Para> + +<Para> +<Figure Id="EXTEND-CATALOGS" Float="1"> +<Title>The major <ProductName>Postgres</ProductName> system catalogs</Title> +<Graphic Align="center" FileRef="catalogs.gif" Format="GIF"></Graphic> +</Figure> + + The Reference Manual gives a more detailed explanation + of these catalogs and their attributes. However, +<XRef LinkEnd="EXTEND-CATALOGS" EndTerm="EXTEND-CATALOGS"> + 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: + +<ItemizedList Mark="bullet" Spacing="compact"> +<ListItem> +<Para> + 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. +</Para> +</ListItem> +<ListItem> +<Para> 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. +</Para> +</ListItem> +<ListItem> +<Para> Types and procedures + are central to the schema. + +<Note> +<Para> +We use the words <FirstTerm>procedure</FirstTerm> and <FirstTerm>function</FirstTerm> more or less +interchangably. +</Para> +</Note> + + Nearly every catalog contains some reference to + instances in one or both of these classes. For + example, <ProductName>Postgres</ProductName> frequently uses type + signatures (e.g., of functions and operators) to + identify unique instances of other catalogs. + +</Para> +</ListItem> +<ListItem> +<Para> 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 + pg_am, pg_amop, pg_amproc, pg_operator and + pg_opclass 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. +</ListItem> +</ItemizedList> + +</Para> +</Chapter> |