<indexterm>
    <primary>crosstab</primary>
   </indexterm>

<synopsis>
crosstab<replaceable>N</replaceable>(text sql)
</synopsis>

    <para>
     The <function>crosstab<replaceable>N</replaceable></function> functions are examples of how
     to set up custom wrappers for the general <function>crosstab</function> function,
     so that you need not write out column names and types in the calling
     <command>SELECT</command> query.  The <filename>tablefunc</filename> module includes
     <function>crosstab2</function>, <function>crosstab3</function>, and
     <function>crosstab4</function>, whose output row types are defined as
    </para>

<programlisting>
CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);
</programlisting>

    <para>
     Thus, these functions can be used directly when the input query produces
     <structfield>row_name</structfield> and <structfield>value</structfield> columns of type
     <type>text</type>, and you want 2, 3, or 4 output values columns.
     In all other ways they behave exactly as described above for the
     general <function>crosstab</function> function.
    </para>

    <para>
     For instance, the example given in the previous section would also
     work as
<programlisting>
SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');
</programlisting>
    </para>

    <para>
     These functions are provided mostly for illustration purposes. You
     can create your own return types and functions based on the
     underlying <function>crosstab()</function> function.  There are two ways
     to do it:

    <itemizedlist>
     <listitem>
      <para>
       Create a composite type describing the desired output columns,
       similar to the examples in
       <filename>contrib/tablefunc/tablefunc--1.0.sql</filename>.
       Then define a
       unique function name accepting one <type>text</type> parameter and returning
       <type>setof your_type_name</type>, but linking to the same underlying
       <function>crosstab</function> C function.  For example, if your source data
       produces row names that are <type>text</type>, and values that are
       <type>float8</type>, and you want 5 value columns:
<programlisting>
CREATE TYPE my_crosstab_float8_5_cols AS (
    my_row_name text,
    my_category_1 float8,
    my_category_2 float8,
    my_category_3 float8,
    my_category_4 float8,
    my_category_5 float8
);

CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
    RETURNS setof my_crosstab_float8_5_cols
    AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
</programlisting>
      </para>
     </listitem>

     <listitem>
      <para>
       Use <literal>OUT</literal> parameters to define the return type implicitly.
       The same example could also be done this way:
<programlisting>
CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
    IN text,
    OUT my_row_name text,
    OUT my_category_1 float8,
    OUT my_category_2 float8,
    OUT my_category_3 float8,
    OUT my_category_4 float8,
    OUT my_category_5 float8)
  RETURNS setof record
  AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
</programlisting>
      </para>
     </listitem>
    </itemizedlist>
    </para>

  </sect3>

  <sect3 id="tablefunc-functions-crosstab-text-2">
   <title><function>crosstab(text, text)</function></title>

   <indexterm>
    <primary>crosstab</primary>
   </indexterm>

<synopsis>
crosstab(text source_sql, text category_sql)
</synopsis>

   <para>
    The main limitation of the single-parameter form of <function>crosstab</function>
    is that it treats all values in a group alike, inserting each value into
    the first available column.  If you want the value
    columns to correspond to specific categories of data, and some groups
    might not have data for some of the categories,