</Para>

<Para>

It is not currently possible to resize an array value except by

complete replacement; for example, we couldn't change a four-

element array value to a five-element value with a single

assignment to array[5].

An array can be enlarged by assigning to an element adjacent to

those already present, or by assigning to a slice that is adjacent

to or overlaps the data already present. Currently, this is only

allowed for one-dimensional arrays, not multidimensional arrays.

For example, if an array value currently has 4 elements, it will

have five elements after an update that assigns to array[5].

</Para>

<Para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.33 2000/08/23 05:59:01 thomas Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.34 2000/08/24 23:36:28 tgl Exp $

-->

<chapter id="datatype">

<thead>

<row>

<entry><productname>Postgres</productname> Type</entry>

<entry><acronym>SQL92</acronym> or <acronym>SQL3</acronym> Type</entry>

<entry><acronym>SQL92</acronym> or <acronym>SQL99</acronym> Type</entry>

<entry>Description</entry>

</row>

</thead>

<row>

<entry>decimal</entry>

<entry>decimal(p,s)</entry>

<entry>exact numericfor p <= 9, s = 0</entry>

<entry>exact numericwith selectable precision</entry>

</row>

<row>

<entry>float4</entry>

<row>

<entry>numeric</entry>

<entry>numeric(p,s)</entry>

<entry>exact numericfor p == 9, s = 0</entry>

<entry>exact numericwith selectable precision</entry>

</row>

<row>

<entry>path</entry>

<entry></entry>

<entry>unique id for indexing and cross-reference</entry>

</row>

<row>

<entry>text</entry>

<entry></entry>

<entry>variable-length character string</entry>

</row>

<row>

<entry>time</entry>

<entry>time [ without time zone ]</entry>

<para>

<productname>Postgres</productname> has features at the forefront of

<acronym>ORDBMS</acronym> development. In addition to

<acronym>SQL3</acronym> conformance, substantial portions

<acronym>SQL99</acronym> conformance, substantial portions

of <acronym>SQL92</acronym> are also supported.

Although we strive for <acronym>SQL92</acronym> compliance,

there are some aspects of the standard

<title>Numeric Types</title>

<para>

Numeric types consist of two- and four-byte integers, four- and eight-byte

Numeric types consist of two-, four-, and eight-byte integers,

four- and eight-byte

floating point numbers and fixed-precision decimals.

</para>

<entry>decimal</entry>

<entry>variable</entry>

<entry>User-specified precision</entry>

<entry>~8000 digits</entry>

<entry>no limit</entry>

</row>

<row>

<entry>float4</entry>

<entry>int8</entry>

<entry>8 bytes</entry>

<entry>Very large range fixed-precision</entry>

<entry>+/- &gt;18 decimal places</entry>

<entry>~18 decimal places</entry>

</row>

<row>

<entry>numeric</entry>

<para>

The <type>int8</type> type may not be available on all platforms since

it relies on compiler support forthis.

it relies on compiler support foreight-byte integers.

</para>

<sect2>

</thead>

<tbody>

<row>

<entry>char</entry>

<entry>"char"</entry>

<entry>1 byte</entry>

<entry><acronym>SQL92</acronym>-compatible</entry>

<entry>Single character</entry>

</row>

<row>

<entry>varchar(n)</entry>

<entry>(4+n) bytes</entry>

<entry>(4+x) bytes</entry>

<entry><acronym>SQL92</acronym>-compatible</entry>

<entry>Variable-length with limit</entry>

</row>

<para>

<productname>Postgres</productname> supports <type>bool</type> as

the <acronym>SQL3</acronym> boolean type.

the <acronym>SQL99</acronym> boolean type.

<type>bool</type> can have one of only two states: 'true' or 'false'.

A third state, 'unknown', is not

implemented and is not suggested in <acronym>SQL3</acronym>;

implemented and is not suggested in <acronym>SQL99</acronym>;

<acronym>NULL</acronym> is an

effective substitute. <type>bool</type> can be used in any boolean expression,

and boolean expressions

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.15 2000/07/22 04:30:27 momjian Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.16 2000/08/24 23:36:29 tgl Exp $

Postgres documentation

-->

CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">ftype</replaceable> [, ...] ] )

RETURNS <replaceable class="parameter">rtype</replaceable>

AS <replaceable class="parameter">obj_file</replaceable> , <replaceable class="parameter">link_symbol</replaceable>

LANGUAGE 'C'

LANGUAGE 'langname'

[ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ]

</synopsis>

<term><replaceable class="parameter">ftype</replaceable></term>

<listitem>

<para>

The data type of function arguments.

The data type(s) ofthefunction's arguments, if any.

The input types may be base or complex types, or

<firstterm>opaque</firstterm>.

<literal>opaque</literal> indicates that the function

accepts arguments ofan invalid type such as <type>char *</type>.

accepts arguments ofa non-SQL type such as <type>char *</type>.

</para>

</listitem>

</varlistentry>

<listitem>

<para>

An optional piece of information about the function, used for

optimization. The only attribute currently supported is

<literal>iscachable</literal>.

<literal>iscachable</literal> indicates that the function always

returns the same result when given the same input values (i.e.,

it does not do database lookups or otherwise use information not

directly present in its parameter list). The optimizer uses

<literal>iscachable</literal> to know whether it is safe to

pre-evaluate a call of the function.

optimization. See below for details.

</para>

</listitem>

</varlistentry>

function. The string <replaceable

class="parameter">obj_file</replaceable> is the name of the file

containing the dynamically loadable object, and <replaceable

class="parameter">link_symbol</replaceable>, is the object's link

symbol which is the same as the name of the function in the C

class="parameter">link_symbol</replaceable> is the object's link

symbol, that is the name of the function in the C

language source code.

</para>

</listitem>

<term><replaceable class="parameter">langname</replaceable></term>

<listitem>

<para>

may be '<literal>C</literal>', '<literal>sql</literal>',

'<literal>internal</literal>'

may be '<literal>sql</literal>',

'<literal>C</literal>', '<literal>newC</literal>',

'<literal>internal</literal>', '<literal>newinternal</literal>',

or '<replaceable class="parameter">plname</replaceable>',

where '<replaceable class="parameter">plname</replaceable>'

is the name of a created procedural language. See

<command>CREATE FUNCTION</command> allows a

<productname>Postgres</productname> user

to register a function

witha database. Subsequently, this user is considered the

withthe database. Subsequently, this user is considered the

owner of the function.

</para>

<refsect2 id="R2-SQL-CREATEFUNCTION-3">

<refsect2info>

<date>2000-08-24</date>

</refsect2info>

<title>

Function Attributes

</title>

<para>

The following items may appear in the WITH clause:

<variablelist>

<varlistentry>

<literal>iscachable</literal>

<listitem>

<para>

<literal>iscachable</literal> indicates that the function always

returns the same result when given the same argument values (i.e.,

it does not do database lookups or otherwise use information not

directly present in its parameter list). The optimizer uses

<literal>iscachable</literal> to know whether it is safe to

pre-evaluate a call of the function.

</para>

</listitem>

</varlistentry>

<varlistentry>

<literal>isstrict</literal>

<listitem>

<para>

<literal>isstrict</literal> indicates that the function always

returns NULL whenever any of its arguments are NULL. If this

attribute is specified, the function is not executed when there

are NULL arguments; instead a NULL result is assumed automatically.

When <literal>isstrict</literal> is not specified, the function will

be called for NULL inputs. It is then the function author's

responsibility to check for NULLs if necessary and respond

appropriately.

</para>

</listitem>

</varlistentry>

</variablelist>

</para>

</refsect2>

<refsect2 id="R2-SQL-CREATEFUNCTION-4">

<refsect2info>

<date>2000-03-25</date>

</refsect2info>

to remove user-defined functions.

</para>

<para>

<productname>Postgres</productname> allows function "overloading";

that is, the same name can be used for several different functions

so long as they have distinct argument types. This facility must

be used with caution for <literal>internal</literal> and

C-language functions, however.

</para>

<para>

The full <acronym>SQL92</acronym> type syntax is allowed for

input arguments and return value. However, some details of the

type specification (e.g. the precision field for

<type>numeric</type> types) are the responsibility of the

underlying function implementation and are silently swallowed

(e.g. not recognized or

(i.e., not recognized or

enforced) by the <command>CREATE FUNCTION</command> command.

</para>

<para>

Two <literal>internal</literal>

<productname>Postgres</productname> allows function "overloading";

that is, the same name can be used for several different functions

so long as they have distinct argument types. This facility must

be used with caution for internal and C-language functions, however.

</para>

<para>

Two <literal>internal</literal> or <literal>newinternal</literal>

functions cannot have the same C name without causing

errors at link time. To get around that, give them different C names

(for example, use the argument types as part of the C names), then

</para>

<para>

When overloading SQL functions with C-language functions, give

each C-language instance of the function a distinct name, and use

Similarly, when overloading SQL function names with multiple C-language

functions, give

each C-language instance of the function a distinct name, then use

the alternative form of the <command>AS</command> clause in the

<command>CREATE FUNCTION</command> syntax to ensure that

overloaded SQL functions names are resolved to the correct

dynamically linked objects.

<command>CREATE FUNCTION</command> syntax to select the appropriate

C-language implementation of each overloaded SQL function.

</para>

<para>

A C function cannot return a set of values.

</para>

</refsect2>

</refsect1>

function is implemented by a dynamically loaded object that was

compiled from C source. For <productname>Postgres</productname> to

find a type conversion function automatically, the sql function has

to have the same name as the return type, and overloading is

to have the same name as the return type, andsooverloading is

unavoidable. The function name is overloaded by using the second

form of the <command>AS</command> clause in the SQL definition:

</para>

Compatibility

</title>

<refsect2 id="R2-SQL-CREATEFUNCTION-4">

<refsect2 id="R2-SQL-CREATEFUNCTION-5">

<refsect2info>

<date>2000-03-25</date>

</refsect2info>

</para>

</refsect2>

<refsect2 id="R2-SQL-CREATEFUNCTION-5">

<refsect2 id="R2-SQL-CREATEFUNCTION-6">

<refsect2info>

<date>2000-03-25</date>

</refsect2info>