path: root/doc/src/sgml/extend.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.18 2002/11/03 01:31:32 momjian Exp $
-->

 <chapter id="extend">
  <title>Extending <acronym>SQL</acronym>: An Overview</title>

   <indexterm zone="extend">
    <primary>extending SQL</primary>
   </indexterm>

  <para>
   In  the  sections  that follow, we will discuss how you
   can extend the <productname>PostgreSQL</productname> 
   <acronym>SQL</acronym> query language by adding:

   <itemizedlist spacing="compact" mark="bullet">
    <listitem>
     <para>
      functions
     </para>
    </listitem>
    <listitem>
     <para>
      data types
     </para>
    </listitem>
    <listitem>
     <para>
      operators
     </para>
    </listitem>
    <listitem>
     <para>
      aggregates
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <sect1 id="extend-how">
   <title>How Extensibility Works</title>

   <para>
    <productname>PostgreSQL</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 tables like any other, but  the  <acronym>DBMS</acronym>  stores
    its  internal  bookkeeping in them.  One key difference
    between <productname>PostgreSQL</productname> and  standard  relational  systems  is
    that <productname>PostgreSQL</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 tables can be  modified  by
    the  user, and since <productname>PostgreSQL</productname> bases its internal operation 
    on these tables, this means that <productname>PostgreSQL</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>PostgreSQL</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 shared library) that implements a new type or  function 
    and <productname>PostgreSQL</productname> will load it as required.  Code written 
    in <acronym>SQL</acronym> is even more trivial to add to the  server.
    This ability to modify its operation <quote>on the fly</quote> makes
    <productname>PostgreSQL</productname> uniquely suited for rapid prototyping  of  new
    applications and storage structures.
   </para>
  </sect1>

  <sect1 id="type-system">
   <title>The <productname>PostgreSQL</productname> Type System</title>

   <indexterm zone="type-system">
    <primary>extending SQL</primary>
    <secondary>types</secondary>
   </indexterm>

   <indexterm zone="type-system">
    <primary>data types</primary>
   </indexterm>

   <para>
    The <productname>PostgreSQL</productname> type system
    can be broken down in several ways.
    Types are divided into base types and composite  types.
    Base  types  are those, like <type>int4</type>, that are implemented
    in a language such as C.  They generally correspond  to
    what are often known as <firstterm>abstract data types</firstterm>; <productname>PostgreSQL</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
    table.
   </para>

   <para>
    <productname>PostgreSQL</productname>  stores  these  types
    in only one way (within the
    file that stores all rows of a table)  but  the
    user can <quote>look inside</quote> at the attributes of these types
    from the query language and optimize their retrieval by
    (for example) defining indexes on the attributes.
    <productname>PostgreSQL</productname>  base  types are further
    divided into built-in
    types and user-defined  types.   Built-in  types  (like
    <type>int4</type>)  are  those  that  are  compiled
    into the system.
    User-defined types are those created by the user in the
    manner to be described later.
   </para>
  </sect1>

  <sect1 id="pg-system-catalogs">
   <title>About the <productname>PostgreSQL</productname> System Catalogs</title>

   <indexterm zone="pg-system-catalogs">
    <primary>catalogs</primary>
   </indexterm>

   <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
    <literal>pg_</literal>.
    The  following  tables 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>PostgreSQL System Catalogs</title>
     <titleabbrev>Catalogs</titleabbrev>
     <tgroup cols="2">
      <thead>
       <row>
	<entry>Catalog Name</entry>
	<entry>Description</entry>
       </row>
      </thead>
      <tbody>
       <row>
	<entry><structname>pg_database</></entry>
	<entry> databases</entry>
       </row>
       <row>
	<entry><structname>pg_class</></entry>
	<entry> tables</entry>
       </row>
       <row>
	<entry><structname>pg_attribute</></entry>
	<entry> table columns</entry>
       </row>
       <row>
	<entry><structname>pg_index</></entry>
	<entry> indexes</entry>
       </row>
       <row>
	<entry><structname>pg_proc</></entry>
	<entry> procedures/functions </entry>
       </row>
       <row>
	<entry><structname>pg_type</></entry>
	<entry> data types (both base and complex)</entry>
       </row>
       <row>
	<entry><structname>pg_operator</></entry>
	<entry> operators</entry>
       </row>
       <row>
	<entry><structname>pg_aggregate</></entry>
	<entry> aggregate functions</entry>
       </row>
       <row>
	<entry><structname>pg_am</></entry>
	<entry> access methods</entry>
       </row>
       <row>
	<entry><structname>pg_amop</></entry>
	<entry> access method operators</entry>
       </row>
       <row>
	<entry><structname>pg_amproc</></entry>
	<entry> access method support functions</entry>
       </row>
       <row>
	<entry><structname>pg_opclass</></entry>
	<entry> access method operator classes</entry>
       </row>
      </tbody>
     </tgroup>
    </table>
   </para>

   <para>
    <figure float="1" id="EXTEND-CATALOGS">
     <title>The major <productname>PostgreSQL</productname> system catalogs</title>
     <mediaobject>
      <imageobject>
       <imagedata fileref="catalogs" align="center">
      </imageobject>
     </mediaobject>
    </figure>

    The <citetitle>Developer's Guide</citetitle> gives a more detailed  explanation
    of  these catalogs and their columns.  However,
    <xref linkend="EXTEND-CATALOGS">
    shows the major entities and their  relationships
    in  the system catalogs.  (Columns 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 spacing="compact" mark="bullet">
     <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
       columns used in the queries form foreign keys
       in other tables.
      </para>
     </listitem>
     <listitem>
      <para>
       Many  different  features  (tables, columns,
       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 interchangeably.
	</para>
       </note>

       Nearly  every catalog contains some reference to
       rows in one or both of these tables.   For
       example,  <productname>PostgreSQL</productname>  frequently  uses type 
       signatures (e.g.,  of  functions  and  operators)  to
       identify unique rows of other catalogs.
      </para>
     </listitem>
     <listitem>
      <para>
       There are many columns and relationships that
       have obvious meanings, but there are many  
       (particularly  those  that  have  to  do with access
       methods) that do not.
      </para>
     </listitem>
    </itemizedlist>
   </para>
  </sect1>
 </chapter>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->