<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Extending SQL</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.0.11 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Server Programming"
HREF="server-programming.html"><LINK
REL="PREVIOUS"
TITLE="Server Programming"
HREF="server-programming.html"><LINK
REL="NEXT"
TITLE="The PostgreSQL Type System"
HREF="extend-type-system.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2007-02-02T03:57:22"></HEAD
><BODY
CLASS="CHAPTER"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.0.11 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="server-programming.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="server-programming.html#AEN29362"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="triggers.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="extend-type-system.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="CHAPTER"
><H1
><A
NAME="EXTEND"
></A
>Chapter 31. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
>31.1. <A
HREF="extend.html#EXTEND-HOW"
>How Extensibility Works</A
></DT
><DT
>31.2. <A
HREF="extend-type-system.html"
>The <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> Type System</A
></DT
><DD
><DL
><DT
>31.2.1. <A
HREF="extend-type-system.html#AEN29422"
>Base Types</A
></DT
><DT
>31.2.2. <A
HREF="extend-type-system.html#AEN29428"
>Composite Types</A
></DT
><DT
>31.2.3. <A
HREF="extend-type-system.html#AEN29433"
>Domains</A
></DT
><DT
>31.2.4. <A
HREF="extend-type-system.html#AEN29439"
>Pseudo-Types</A
></DT
><DT
>31.2.5. <A
HREF="extend-type-system.html#EXTEND-TYPES-POLYMORPHIC"
>Polymorphic Types</A
></DT
></DL
></DD
><DT
>31.3. <A
HREF="xfunc.html"
>User-Defined Functions</A
></DT
><DT
>31.4. <A
HREF="xfunc-sql.html"
>Query Language (<ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>) Functions</A
></DT
><DD
><DL
><DT
>31.4.1. <A
HREF="xfunc-sql.html#AEN29558"
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Functions on Base Types</A
></DT
><DT
>31.4.2. <A
HREF="xfunc-sql.html#AEN29579"
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Functions on Composite Types</A
></DT
><DT
>31.4.3. <A
HREF="xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS"
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Functions as Table Sources</A
></DT
><DT
>31.4.4. <A
HREF="xfunc-sql.html#AEN29644"
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Functions Returning Sets</A
></DT
><DT
>31.4.5. <A
HREF="xfunc-sql.html#AEN29662"
>Polymorphic <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Functions</A
></DT
></DL
></DD
><DT
>31.5. <A
HREF="xfunc-overload.html"
>Function Overloading</A
></DT
><DT
>31.6. <A
HREF="xfunc-volatility.html"
>Function Volatility Categories</A
></DT
><DT
>31.7. <A
HREF="xfunc-pl.html"
>Procedural Language Functions</A
></DT
><DT
>31.8. <A
HREF="xfunc-internal.html"
>Internal Functions</A
></DT
><DT
>31.9. <A
HREF="xfunc-c.html"
>C-Language Functions</A
></DT
><DD
><DL
><DT
>31.9.1. <A
HREF="xfunc-c.html#XFUNC-C-DYNLOAD"
>Dynamic Loading</A
></DT
><DT
>31.9.2. <A
HREF="xfunc-c.html#XFUNC-C-BASETYPE"
>Base Types in C-Language Functions</A
></DT
><DT
>31.9.3. <A
HREF="xfunc-c.html#AEN30132"
>Calling Conventions Version 0 for C-Language Functions</A
></DT
><DT
>31.9.4. <A
HREF="xfunc-c.html#AEN30159"
>Calling Conventions Version 1 for C-Language Functions</A
></DT
><DT
>31.9.5. <A
HREF="xfunc-c.html#AEN30217"
>Writing Code</A
></DT
><DT
>31.9.6. <A
HREF="xfunc-c.html#DFUNC"
>Compiling and Linking Dynamically-Loaded Functions</A
></DT
><DT
>31.9.7. <A
HREF="xfunc-c.html#XFUNC-C-PGXS"
>Extension Building Infrastructure</A
></DT
><DT
>31.9.8. <A
HREF="xfunc-c.html#AEN30562"
>Composite-Type Arguments in C-Language Functions</A
></DT
><DT
>31.9.9. <A
HREF="xfunc-c.html#AEN30586"
>Returning Rows (Composite Types) from C-Language Functions</A
></DT
><DT
>31.9.10. <A
HREF="xfunc-c.html#XFUNC-C-RETURN-SET"
>Returning Sets from C-Language Functions</A
></DT
><DT
>31.9.11. <A
HREF="xfunc-c.html#AEN30683"
>Polymorphic Arguments and Return Types</A
></DT
></DL
></DD
><DT
>31.10. <A
HREF="xaggr.html"
>User-Defined Aggregates</A
></DT
><DT
>31.11. <A
HREF="xtypes.html"
>User-Defined Types</A
></DT
><DT
>31.12. <A
HREF="xoper.html"
>User-Defined Operators</A
></DT
><DT
>31.13. <A
HREF="xoper-optimization.html"
>Operator Optimization Information</A
></DT
><DD
><DL
><DT
>31.13.1. <A
HREF="xoper-optimization.html#AEN30846"
><TT
CLASS="LITERAL"
>COMMUTATOR</TT
></A
></DT
><DT
>31.13.2. <A
HREF="xoper-optimization.html#AEN30881"
><TT
CLASS="LITERAL"
>NEGATOR</TT
></A
></DT
><DT
>31.13.3. <A
HREF="xoper-optimization.html#AEN30897"
><TT
CLASS="LITERAL"
>RESTRICT</TT
></A
></DT
><DT
>31.13.4. <A
HREF="xoper-optimization.html#AEN30947"
><TT
CLASS="LITERAL"
>JOIN</TT
></A
></DT
><DT
>31.13.5. <A
HREF="xoper-optimization.html#AEN30979"
><TT
CLASS="LITERAL"
>HASHES</TT
></A
></DT
><DT
>31.13.6. <A
HREF="xoper-optimization.html#AEN30999"
><TT
CLASS="LITERAL"
>MERGES</TT
> (<TT
CLASS="LITERAL"
>SORT1</TT
>, <TT
CLASS="LITERAL"
>SORT2</TT
>, <TT
CLASS="LITERAL"
>LTCMP</TT
>, <TT
CLASS="LITERAL"
>GTCMP</TT
>)</A
></DT
></DL
></DD
><DT
>31.14. <A
HREF="xindex.html"
>Interfacing Extensions To Indexes</A
></DT
><DD
><DL
><DT
>31.14.1. <A
HREF="xindex.html#XINDEX-IM"
>Index Methods and Operator Classes</A
></DT
><DT
>31.14.2. <A
HREF="xindex.html#XINDEX-STRATEGIES"
>Index Method Strategies</A
></DT
><DT
>31.14.3. <A
HREF="xindex.html#XINDEX-SUPPORT"
>Index Method Support Routines</A
></DT
><DT
>31.14.4. <A
HREF="xindex.html#XINDEX-EXAMPLE"
>An Example</A
></DT
><DT
>31.14.5. <A
HREF="xindex.html#XINDEX-OPCLASS-CROSSTYPE"
>Cross-Data-Type Operator Classes</A
></DT
><DT
>31.14.6. <A
HREF="xindex.html#XINDEX-OPCLASS-DEPENDENCIES"
>System Dependencies on Operator Classes</A
></DT
><DT
>31.14.7. <A
HREF="xindex.html#XINDEX-OPCLASS-FEATURES"
>Special Features of Operator Classes</A
></DT
></DL
></DD
></DL
></DIV
><A
NAME="AEN29370"
></A
><P
> In the sections that follow, we will discuss how you
can extend the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
<ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> query language by adding:
<P
></P
></P><UL
COMPACT="COMPACT"
><LI
STYLE="list-style-type: disc"
><P
> functions (starting in <A
HREF="xfunc.html"
>Section 31.3</A
>)
</P
></LI
><LI
STYLE="list-style-type: disc"
><P
> aggregates (starting in <A
HREF="xaggr.html"
>Section 31.10</A
>)
</P
></LI
><LI
STYLE="list-style-type: disc"
><P
> data types (starting in <A
HREF="xtypes.html"
>Section 31.11</A
>)
</P
></LI
><LI
STYLE="list-style-type: disc"
><P
> operators (starting in <A
HREF="xoper.html"
>Section 31.12</A
>)
</P
></LI
><LI
STYLE="list-style-type: disc"
><P
> operator classes for indexes (starting in <A
HREF="xindex.html"
>Section 31.14</A
>)
</P
></LI
></UL
><P>
</P
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="EXTEND-HOW"
>31.1. How Extensibility Works</A
></H1
><P
> <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> is extensible because its operation is
catalog-driven. If you are familiar with standard
relational database 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
CLASS="ACRONYM"
>DBMS</ACRONYM
> stores
its internal bookkeeping in them. One key difference
between <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> and standard relational database systems is
that <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> stores much more information in its
catalogs: not only information about tables and columns,
but also information about data types, functions, access
methods, and so on. These tables can be modified by
the user, and since <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> bases its operation
on these tables, this means that <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can be
extended by users. By comparison, conventional
database systems can only be extended by changing hardcoded
procedures in the source code or by loading modules
specially written by the <ACRONYM
CLASS="ACRONYM"
>DBMS</ACRONYM
> vendor.
</P
><P
> The <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> server can moreover
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
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> will load it as required.
Code written in <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> is even more trivial to add
to the server. This ability to modify its operation <SPAN
CLASS="QUOTE"
>"on the
fly"</SPAN
> makes <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> uniquely
suited for rapid prototyping of new applications and storage
structures.
</P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="server-programming.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="extend-type-system.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Server Programming</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="server-programming.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>The <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> Type System</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
