xfunc.sgml

关系型数据库 Postgresql 6.5.2

     by  reference.   All  variable-length  types must begin
     with a length field of exactly 4 bytes, and all data to
     be  stored within that type must be located in the memory 
     immediately  following  that  length  field.   The
     length  field  is  the  total  length  of the structure
     (i.e.,  it  includes  the  size  of  the  length  field
     itself).  We can define the text type as follows:
</Para>

<Para>
<ProgramListing>
         typedef struct {
             int4 length;
             char data[1];
         } text;
</ProgramListing>
</Para>

<Para>
     Obviously,  the  data  field is not long enough to hold
     all possible strings -- it's impossible to declare such
     a  structure  in  <Acronym>C</Acronym>.  When manipulating 
     variable-length types, we must  be  careful  to  allocate  
     the  correct amount  of memory and initialize the length field.  
     For example, if we wanted to  store  40  bytes  in  a  text
     structure, we might use a code fragment like this:
<ProgramListing>
         #include "postgres.h"
         ...
         char buffer[40]; /* our source data */
         ...
         text *destination = (text *) palloc(VARHDRSZ + 40);
         destination-&gt;length = VARHDRSZ + 40;
         memmove(destination-&gt;data, buffer, 40);
         ...
</ProgramListing>
</Para>

<Para>
     Now that we've gone over all of the possible structures
     for base types, we can show some examples of real functions. 
     Suppose <FileName>funcs.c</FileName> look like:
<ProgramListing>
         #include &lt;string.h&gt;
         #include "postgres.h"

         /* By Value */
         
         int
         add_one(int arg)
         {
             return(arg + 1);
         }
         
         /* By Reference, Fixed Length */
         
         Point *
         makepoint(Point *pointx, Point *pointy )
         {
             Point     *new_point = (Point *) palloc(sizeof(Point));
        
             new_point->x = pointx->x;
             new_point->y = pointy->y;
                
             return new_point;
         }
        
         /* By Reference, Variable Length */
         
         text *
         copytext(text *t)
         {
             /*
              * VARSIZE is the total size of the struct in bytes.
              */
             text *new_t = (text *) palloc(VARSIZE(t));
             memset(new_t, 0, VARSIZE(t));
             VARSIZE(new_t) = VARSIZE(t);
             /*
              * VARDATA is a pointer to the data region of the struct.
              */
             memcpy((void *) VARDATA(new_t), /* destination */
                    (void *) VARDATA(t),     /* source */
                    VARSIZE(t)-VARHDRSZ);        /* how many bytes */
             return(new_t);
         }
         
         text *
         concat_text(text *arg1, text *arg2)
         {
             int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
             text *new_text = (text *) palloc(new_text_size);

             memset((void *) new_text, 0, new_text_size);
             VARSIZE(new_text) = new_text_size;
             strncpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1)-VARHDRSZ);
             strncat(VARDATA(new_text), VARDATA(arg2), VARSIZE(arg2)-VARHDRSZ);
             return (new_text);
         }
</ProgramListing>
</Para>

<Para>
     On <Acronym>OSF/1</Acronym> we would type:
     
<ProgramListing>
         CREATE FUNCTION add_one(int4) RETURNS int4
              AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c';

         CREATE FUNCTION makepoint(point, point) RETURNS point
              AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c';
    
         CREATE FUNCTION concat_text(text, text) RETURNS text
              AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c';
                                  
         CREATE FUNCTION copytext(text) RETURNS text
              AS 'PGROOT/tutorial/funcs.so' LANGUAGE 'c';
</ProgramListing>
</Para>

<Para>
     On  other  systems,  we might have to make the filename
     end in .sl (to indicate that it's a shared library).
</Para>
</Sect2>

<Sect2>
<Title>Programming Language Functions on Composite Types</Title>

<Para>
     Composite types do not  have  a  fixed  layout  like  C
     structures.   Instances of a composite type may contain
     null fields.  In addition,  composite  types  that  are
     part  of  an  inheritance  hierarchy may have different
     fields than other members of the same inheritance hierarchy.    
     Therefore,  <ProductName>Postgres</ProductName>  provides  
     a  procedural interface for accessing fields of composite types  
     from C.  As <ProductName>Postgres</ProductName> processes 
     a set of instances, each instance will be passed into your 
     function as an  opaque  structure of type <Acronym>TUPLE</Acronym>.
     Suppose we want to write a function to answer the query
<ProgramListing>
         * SELECT name, c_overpaid(EMP, 1500) AS overpaid
           FROM EMP
           WHERE name = 'Bill' or name = 'Sam';
</ProgramListing>
     In the query above, we can define c_overpaid as:
     
<ProgramListing>
         #include "postgres.h"
         #include "executor/executor.h"  /* for GetAttributeByName() */
         
         bool
         c_overpaid(TupleTableSlot *t, /* the current instance of EMP */
                    int4 limit)
         {
             bool isnull = false;
             int4 salary;
             salary = (int4) GetAttributeByName(t, "salary", &amp;isnull);
             if (isnull)
                 return (false);
             return(salary &gt; limit);
         }
</ProgramListing>
</Para>

<Para>
     <Acronym>GetAttributeByName</Acronym> is the 
     <ProductName>Postgres</ProductName> system function that
     returns attributes out of the current instance.  It has
     three arguments: the argument of type TUPLE passed into
     the  function, the name of the desired attribute, and a
     return parameter that describes whether  the  attribute
     is  null.   <Acronym>GetAttributeByName</Acronym> will 
     align data properly so you can cast its return value to 
     the  desired  type. For  example, if you have an attribute 
     name which is of the type name, the <Acronym>GetAttributeByName</Acronym> 
     call would look like:
<ProgramListing>
         char *str;
         ...
         str = (char *) GetAttributeByName(t, "name", &amp;isnull)
</ProgramListing>
</Para>

<Para>
     The  following  query  lets  <ProductName>Postgres</ProductName>  
     know  about  the c_overpaid function:
<ProgramListing>
         * CREATE FUNCTION c_overpaid(EMP, int4) RETURNS bool
              AS 'PGROOT/tutorial/obj/funcs.so' LANGUAGE 'c';
</ProgramListing>
</Para>

<Para>
     While there are ways to construct new instances or modify  
     existing instances from within a C function, these
     are far too complex to discuss in this manual.
</Para>
</Sect2>

<Sect2>
<Title>Caveats</Title>

<Para>
     We now turn to the more difficult task of writing  
     programming  language  functions.  Be warned: this section
     of the manual will not make you a programmer.  You must
     have  a  good  understanding of <Acronym>C</Acronym> 
     (including the use of pointers and the malloc memory manager)  
     before  trying to write <Acronym>C</Acronym> functions for 
     use with <ProductName>Postgres</ProductName>. While  it may 
     be possible to load functions written in languages other 
     than <Acronym>C</Acronym> into  <ProductName>Postgres</ProductName>,  
     this  is  often difficult  (when  it  is possible at all) 
     because other languages, such as <Acronym>FORTRAN</Acronym> 
     and <Acronym>Pascal</Acronym> often do not follow the same 
     "calling convention" as <Acronym>C</Acronym>.  That is, other
     languages  do  not  pass  argument  and  return  values
     between functions in the same way.  For this reason, we
     will assume that your  programming  language  functions
     are written in <Acronym>C</Acronym>.
     The  basic  rules  for building <Acronym>C</Acronym> functions 
     are as follows:

<ItemizedList>
<ListItem>
<Para>
            Most of the header (include) files for 
            <ProductName>Postgres</ProductName>
            should      already      be     installed     in
            <FileName>PGROOT/include</FileName>  (see  Figure  2).
            You should always include
            
<ProgramListing>
                -I$PGROOT/include
</ProgramListing>
            on  your  cc  command lines.  Sometimes, you may
            find that you require header files that  are  in
            the  server source itself (i.e., you need a file
            we neglected to install in include).   In  those
            cases you may need to add one or more of
<ProgramListing>
                -I$PGROOT/src/backend
                -I$PGROOT/src/backend/include
                -I$PGROOT/src/backend/port/&lt;PORTNAME&gt;
                -I$PGROOT/src/backend/obj
</ProgramListing>
            (where &lt;PORTNAME&gt; is the name of the port, e.g.,
            alpha or sparc).
</para>
</ListItem>
<ListItem>
<Para>      When allocating memory, use  the
            <ProductName>Postgres</ProductName>
            routines  palloc  and  pfree  instead of the 
            corresponding <Acronym>C</Acronym> library  routines  
            malloc  and  free.
            The  memory  allocated  by  palloc will be freed
            automatically at the end  of  each  transaction,
            preventing memory leaks.
</Para>
      </ListItem>
      <ListItem>
<Para>   Always  zero  the bytes of your structures using
            memset or bzero.  Several routines (such as  the
            hash access method, hash join and the sort algorithm) 
            compute functions of the  raw  bits  contained  in 
            your structure.  Even if you initialize all fields 
            of your structure, there  may  be
            several bytes of alignment padding (holes in the
            structure) that may contain garbage values.
</Para>
      </ListItem>
      <ListItem>
<Para>      Most of the internal <ProductName>Postgres</ProductName> 
            types are declared in  postgres.h,  so  it's a good 
            idea to always include that file as well.  Including 
            postgres.h will also include elog.h and palloc.h for you.
</Para>
      </ListItem>
      <ListItem>
<Para>      Compiling and loading your object code  so  that
            it  can  be  dynamically  loaded  into  
            <ProductName>Postgres</ProductName>
            always requires special flags.  See  Appendix  A
            for  a  detailed explanation of how to do it for
            your particular operating system.
</Para>
</ListItem>
</ItemizedList>
</Para>
</Sect2>
</sect1>
</chapter>