xindex.sgml

关系型数据库 Postgresql 6.5.2

  </para>

  <para>
   The other important point is that all the operator functions return
   Boolean values.  The access methods rely on this fact.  (On the other
   hand, the support function returns whatever the particular access method
   expects -- in this case, a signed integer.) The final routine in the
   file is the "support routine" mentioned when we discussed the amsupport
   attribute of the <filename>pg_am</filename> class.  We will use this
   later on.  For now, ignore it.
  </para>

  <para>
   <programlisting>
CREATE FUNCTION complex_abs_eq(complex_abs, complex_abs)
              RETURNS bool
              AS 'PGROOT/tutorial/obj/complex.so'
              LANGUAGE 'c';
   </programlisting>
  </para>

  <para>
   Now define the operators that use them.  As noted, the operator names
   must be unique among all operators that take two <filename>int4</filename>
   operands.  In order to see if the operator names listed below are taken,
   we can do a query  on <filename>pg_operator</filename>:

   <programlisting>
    /*
     * this query uses the regular expression operator (~)
     * to find three-character operator names that end in
     * the character &amp;
     */
    SELECT *
     FROM pg_operator
     WHERE oprname ~ '^..&amp;$'::text;
   </programlisting>

  </para>

  <para>
   to see if your name is taken for the types you want.  The important
   things here are the procedure (which are the <acronym>C</acronym>
   functions defined above) and the restriction and join selectivity
   functions.  You should just use the ones used below--note that there
   are different such functions for the less-than, equal, and greater-than
   cases.  These must be supplied, or the access method will crash when it
   tries to use the operator.  You should copy the names for restrict and
   join, but use the procedure names you defined in the last step.

   <programlisting>
CREATE OPERATOR = (
     leftarg = complex_abs, rightarg = complex_abs,
     procedure = complex_abs_eq,
     restrict = eqsel, join = eqjoinsel
         )
   </programlisting>
  </para>

  <para>
   Notice that five operators corresponding to less,  less equal, equal,
   greater, and greater equal are defined.
  </para>

  <para>
   We're just about finished. the last thing we need to do is to update
   the <filename>pg_amop</filename> relation.  To do this, we need the
   following attributes:

   <table tocentry="1">
    <title><filename>pg_amproc</filename> Schema</title>
    <titleabbrev><filename>pg_amproc</filename></titleabbrev>
    <tgroup cols="2">
     <thead>
      <row>
       <entry>Attribute</entry>
       <entry>Description</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry>amopid</entry>
       <entry>the <filename>oid</filename> of the <filename>pg_am</filename> instance
	for  B-tree (== 403, see above)</entry>
      </row>
      <row>
       <entry>amopclaid</entry>
       <entry>the <filename>oid</filename> of the
	<filename>pg_opclass</filename>  instance for <filename>complex_abs_ops</filename>
	(== whatever you got instead  of <filename>17314</filename>, see above)</entry>
      </row>
      <row>
       <entry>amopopr</entry>
       <entry>the <filename>oid</filename>s of the  operators  for the opclass
	(which we'll get in just a minute)</entry>
      </row>
      <row>
       <entry>amopselect, amopnpages</entry>
       <entry>cost functions</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   The cost functions are used by the query optimizer to decide whether or
   not to use a given index in a scan.  Fortunately, these already exist.
   The two functions we'll use are <filename>btreesel</filename>, which
   estimates the selectivity of the <acronym>B-tree</acronym>, and
   <filename>btreenpage</filename>, which estimates the number of pages a
   search will touch in the tree.
  </para>

  <para>
   So we need the <filename>oid</filename>s of the operators we just
   defined.  We'll look up the names of all the operators that take
   two <filename>complex</filename>es, and pick ours out:
   
   <programlisting>
    SELECT o.oid AS opoid, o.oprname
     INTO TABLE complex_ops_tmp
     FROM pg_operator o, pg_type t
     WHERE o.oprleft = t.oid and o.oprright = t.oid
      and t.typname = 'complex_abs';

         +------+---------+
         |oid   | oprname |
         +------+---------+
         |17321 | &lt;       |
         +------+---------+
         |17322 | &lt;=      |
         +------+---------+
         |17323 |  =      |
         +------+---------+
         |17324 | &gt;=      |
         +------+---------+
         |17325 | &gt;       |
         +------+---------+
   </programlisting>

   (Again, some of your <filename>oid</filename> numbers will almost
   certainly be different.)  The operators we are interested in are those
   with <filename>oid</filename>s 17321 through 17325.  The values you
   get will probably be different, and you should substitute them for the
   values below.  We will do this with a select statement.
  </para>

  <para>
   Now we're ready to update <filename>pg_amop</filename> with our new
   operator class.  The most important thing in this entire discussion
   is that the operators are ordered, from less equal through greater
   equal, in <filename>pg_amop</filename>.  We add the instances we need:

   <programlisting>
    INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
                amopselect, amopnpages) 
        SELECT am.oid, opcl.oid, c.opoid, 1,
                'btreesel'::regproc, 'btreenpage'::regproc
        FROM pg_am am, pg_opclass opcl, complex_abs_ops_tmp c
        WHERE amname = 'btree' AND
            opcname = 'complex_abs_ops' AND
            c.oprname = '<';
   </programlisting>

   Now do this for the other operators substituting for the "1" in the
   third line above and the "<" in the last line.  Note the order:
   "less than" is 1, "less than or equal" is 2, "equal" is 3, "greater
   than or equal" is 4, and "greater than" is 5.
  </para>

  <para>
   The next step is registration of the "support routine" previously
   described in our discussion of <filename>pg_am</filename>.  The
   <filename>oid</filename> of this support routine is stored in the
   <filename>pg_amproc</filename> class, keyed by the access method
   <filename>oid</filename> and the operator class <filename>oid</filename>.
   First, we need to register the function in
   <productname>Postgres</productname> (recall that we put the
   <acronym>C</acronym> code that implements this routine in the bottom of
   the file in which we implemented the operator routines):

   <programlisting>
    CREATE FUNCTION complex_abs_cmp(complex, complex)
     RETURNS int4
     AS 'PGROOT/tutorial/obj/complex.so'
     LANGUAGE 'c';

    SELECT oid, proname FROM pg_proc
     WHERE proname = 'complex_abs_cmp';

         +------+-----------------+
         |oid   | proname         |
         +------+-----------------+
         |17328 | complex_abs_cmp |
         +------+-----------------+
   </programlisting>

   (Again, your <filename>oid</filename> number will probably be different
   and you should substitute the value you see for the value below.)
   We can add the new instance as follows:

   <programlisting>
    INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
        SELECT a.oid, b.oid, c.oid, 1
            FROM pg_am a, pg_opclass b, pg_proc c
            WHERE a.amname = 'btree' AND
                b.opcname = 'complex_abs_ops' AND
                c.proname = 'complex_abs_cmp';
   </programlisting>
  </para>

  <para>
   Now we need to add a hashing strategy to allow the type to be indexed.
   We do this by using another type in pg_am but we reuse the sames ops.

   <programlisting>
    INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
                amopselect, amopnpages)
        SELECT am.oid, opcl.oid, c.opoid, 1,
                'hashsel'::regproc, 'hashnpage'::regproc
        FROM pg_am am, pg_opclass opcl, complex_abs_ops_tmp c
        WHERE amname = 'hash' AND
            opcname = 'complex_abs_ops' AND
            c.oprname = '=';
   </programlisting>
  </para>

  <para>
   In order to use this index in a where clause, we need to modify the
   <filename>pg_operator</filename> class as follows.

   <programlisting>
    UPDATE pg_operator
        SET oprrest = 'eqsel'::regproc, oprjoin = 'eqjoinsel'
        WHERE oprname = '=' AND
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');
    
    UPDATE pg_operator
        SET oprrest = 'neqsel'::regproc, oprjoin = 'neqjoinsel'
        WHERE oprname = '<filename>' AND
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');
    
    UPDATE pg_operator
        SET oprrest = 'neqsel'::regproc, oprjoin = 'neqjoinsel'
        WHERE oprname = '<filename>' AND
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');
    
    UPDATE pg_operator
        SET oprrest = 'intltsel'::regproc, oprjoin = 'intltjoinsel'
        WHERE oprname = '<' AND 
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');
    
    UPDATE pg_operator
        SET oprrest = 'intltsel'::regproc, oprjoin = 'intltjoinsel'
        WHERE oprname = '<=' AND
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');
    
    UPDATE pg_operator
        SET oprrest = 'intgtsel'::regproc, oprjoin = 'intgtjoinsel'
        WHERE oprname = '>' AND
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');
    
    UPDATE pg_operator
        SET oprrest = 'intgtsel'::regproc, oprjoin = 'intgtjoinsel'
        WHERE oprname = '>=' AND
            oprleft = oprright AND
            oprleft = (SELECT oid FROM pg_type WHERE typname = 'complex_abs');</filename></filename>
   </programlisting> 
  </para>

  <para>
   And last (Finally!) we register a description of this type.

   <programlisting>
    INSERT INTO pg_description (objoid, description) 
    SELECT oid, 'Two part G/L account'
	    FROM pg_type WHERE typname = 'complex_abs';
   </programlisting> 
  </para>

 </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:
-->