diff options
| -rw-r--r-- | doc/src/sgml/array.sgml | 338 | ||||
| -rw-r--r-- | doc/src/sgml/datatype.sgml | 21 | ||||
| -rw-r--r-- | doc/src/sgml/extend.sgml | 120 | ||||
| -rw-r--r-- | doc/src/sgml/func.sgml | 43 | ||||
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 168 | ||||
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 91 | ||||
| -rw-r--r-- | doc/src/sgml/xaggr.sgml | 58 | ||||
| -rw-r--r-- | doc/src/sgml/xfunc.sgml | 171 |
8 files changed, 722 insertions, 288 deletions
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index a0c93aedeb1..fabf2e732cd 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.28 2003/06/27 00:33:25 tgl Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/array.sgml,v 1.29 2003/08/09 22:50:21 tgl Exp $ --> <sect1 id="arrays"> <title>Arrays</title> @@ -36,6 +36,41 @@ CREATE TABLE sal_emp ( <type>text</type> (<structfield>schedule</structfield>), which represents the employee's weekly schedule. </para> + + <para> + The syntax for <command>CREATE TABLE</command> allows the exact size of + arrays to be specified, for example: + +<programlisting> +CREATE TABLE tictactoe ( + squares integer[3][3] +); +</programlisting> + + However, the current implementation does not enforce the array size + limits --- the behavior is the same as for arrays of unspecified + length. + </para> + + <para> + Actually, the current implementation does not enforce the declared + number of dimensions either. Arrays of a particular element type are + all considered to be of the same type, regardless of size or number + of dimensions. So, declaring number of dimensions or sizes in + <command>CREATE TABLE</command> is simply documentation, it does not + affect runtime behavior. + </para> + + <para> + An alternative, SQL99-standard syntax may be used for one-dimensional arrays. + <structfield>pay_by_quarter</structfield> could have been defined as: +<programlisting> + pay_by_quarter integer ARRAY[4], +</programlisting> + This syntax requires an integer constant to denote the array size. + As before, however, <productname>PostgreSQL</> does not enforce the + size restriction. + </para> </sect2> <sect2> @@ -43,9 +78,11 @@ CREATE TABLE sal_emp ( <para> Now we can show some <command>INSERT</command> statements. To write an array - value, we enclose the element values within curly braces and separate them - by commas. If you know C, this is not unlike the syntax for - initializing structures. (More details appear below.) + value as a literal constant, we enclose the element values within curly + braces and separate them by commas. (If you know C, this is not unlike the + C syntax for initializing structures.) We may put double quotes around any + element value, and must do so if it contains commas or curly braces. + (More details appear below.) <programlisting> INSERT INTO sal_emp @@ -90,7 +127,7 @@ SELECT * FROM sal_emp; </note> <para> - The <command>ARRAY</command> expression syntax may also be used: + The <literal>ARRAY</literal> expression syntax may also be used: <programlisting> INSERT INTO sal_emp VALUES ('Bill', @@ -109,29 +146,27 @@ SELECT * FROM sal_emp; (2 rows) </programlisting> Note that with this syntax, multidimensional arrays must have matching - extents for each dimension. This eliminates the missing-array-elements - problem above. For example: + extents for each dimension. A mismatch causes an error report, rather than + silently discarding values as in the previous case. + For example: <programlisting> INSERT INTO sal_emp VALUES ('Carol', ARRAY[20000, 25000, 25000, 25000], ARRAY[['talk', 'consult'], ['meeting']]); -ERROR: Multidimensional arrays must have array expressions with matching dimensions +ERROR: multidimensional arrays must have array expressions with matching dimensions </programlisting> - Also notice that string literals are single quoted instead of double quoted. + Also notice that the array elements are ordinary SQL constants or + expressions; for instance, string literals are single quoted, instead of + double quoted as they would be in an array literal. The <literal>ARRAY</> + expression syntax is discussed in more detail in <xref + linkend="sql-syntax-array-constructors">. </para> - <note> - <para> - The examples in the rest of this section are based on the - <command>ARRAY</command> expression syntax <command>INSERT</command>s. - </para> - </note> - </sect2> <sect2> - <title>Array Value References</title> + <title>Accessing Arrays</title> <para> Now, we can run some queries on the table. @@ -195,7 +230,7 @@ SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill'; represent an array slice if any of the subscripts are written in the form <literal><replaceable>lower</replaceable>:<replaceable>upper</replaceable></literal>. A lower bound of 1 is assumed for any subscript where only one value - is specified. Another example follows: + is specified, as in this example: <programlisting> SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; schedule @@ -206,17 +241,38 @@ SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill'; </para> <para> - Additionally, we can also access a single arbitrary array element of - a one-dimensional array with the <function>array_subscript</function> - function: + The current dimensions of any array value can be retrieved with the + <function>array_dims</function> function: + <programlisting> -SELECT array_subscript(pay_by_quarter, 2) FROM sal_emp WHERE name = 'Bill'; - array_subscript ------------------ - 10000 +SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; + + array_dims +------------ + [1:2][1:1] +(1 row) +</programlisting> + + <function>array_dims</function> produces a <type>text</type> result, + which is convenient for people to read but perhaps not so convenient + for programs. Dimensions can also be retrieved with + <function>array_upper</function> and <function>array_lower</function>, + which return the upper and lower bound of a + specified array dimension, respectively. + +<programlisting> +SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol'; + + array_upper +------------- + 2 (1 row) </programlisting> </para> + </sect2> + + <sect2> + <title>Modifying Arrays</title> <para> An array value can be replaced completely: @@ -226,22 +282,13 @@ UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol'; </programlisting> - or using the <command>ARRAY</command> expression syntax: + or using the <literal>ARRAY</literal> expression syntax: <programlisting> UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; </programlisting> - <note> - <para> - Anywhere you can use the <quote>curly braces</quote> array syntax, - you can also use the <command>ARRAY</command> expression syntax. The - remainder of this section will illustrate only one or the other, but - not both. - </para> - </note> - An array may also be updated at a single element: <programlisting> @@ -256,34 +303,27 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; </programlisting> - A one-dimensional array may also be updated with the - <function>array_assign</function> function: - -<programlisting> -UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000) - WHERE name = 'Bill'; -</programListing> </para> <para> - An array can be enlarged by assigning to an element adjacent to + A stored array value 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. For example, if an array - value currently has 4 elements, it will have five elements after an - update that assigns to <literal>array[5]</>. Currently, enlargement in - this fashion is only allowed for one-dimensional arrays, not - multidimensional arrays. + to or overlaps the data already present. For example, if array + <literal>myarray</> currently has 4 elements, it will have five + elements after an update that assigns to <literal>myarray[5]</>. + Currently, enlargement in this fashion is only allowed for one-dimensional + arrays, not multidimensional arrays. </para> <para> Array slice assignment allows creation of arrays that do not use one-based - subscripts. For example one might assign to <literal>array[-2:7]</> to + subscripts. For example one might assign to <literal>myarray[-2:7]</> to create an array with subscript values running from -2 to 7. </para> <para> - An array can also be enlarged by using the concatenation operator, - <command>||</command>. + New array values can also be constructed by using the concatenation operator, + <literal>||</literal>. <programlisting> SELECT ARRAY[1,2] || ARRAY[3,4]; ?column? @@ -299,7 +339,7 @@ SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; </programlisting> The concatenation operator allows a single element to be pushed on to the - beginning or end of a one-dimensional array. It also allows two + beginning or end of a one-dimensional array. It also accepts two <replaceable>N</>-dimensional arrays, or an <replaceable>N</>-dimensional and an <replaceable>N+1</>-dimensional array. In the former case, the two <replaceable>N</>-dimension arrays become outer elements of an @@ -307,12 +347,13 @@ SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; <replaceable>N</>-dimensional array is added as either the first or last outer element of the <replaceable>N+1</>-dimensional array. - The array is extended in the direction of the push. Hence, by pushing - onto the beginning of an array with a one-based subscript, a zero-based - subscript array is created: + When extending an array by concatenation, the subscripts of its existing + elements are preserved. For example, when pushing + onto the beginning of an array with one-based subscripts, the resulting + array has zero-based subscripts: <programlisting> -SELECT array_dims(t.f) FROM (SELECT 1 || ARRAY[2,3] AS f) AS t; +SELECT array_dims(1 || ARRAY[2,3]); array_dims ------------ [0:2] @@ -321,7 +362,7 @@ SELECT array_dims(t.f) FROM (SELECT 1 || ARRAY[2,3] AS f) AS t; </para> <para> - An array can also be enlarged by using the functions + An array can also be constructed by using the functions <function>array_prepend</function>, <function>array_append</function>, or <function>array_cat</function>. The first two only support one-dimensional arrays, but <function>array_cat</function> supports multidimensional arrays. @@ -362,60 +403,6 @@ SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); {{5,6},{1,2},{3,4}} </programlisting> </para> - - <para> - The syntax for <command>CREATE TABLE</command> allows fixed-length - arrays to be defined: - -<programlisting> -CREATE TABLE tictactoe ( - squares integer[3][3] -); -</programlisting> - - However, the current implementation does not enforce the array size - limits --- the behavior is the same as for arrays of unspecified - length. - </para> - - <para> - An alternative syntax for one-dimensional arrays may be used. - <structfield>pay_by_quarter</structfield> could have been defined as: -<programlisting> - pay_by_quarter integer ARRAY[4], -</programlisting> - This syntax may <emphasis>only</emphasis> be used with the integer - constant to denote the array size. - </para> - - <para> - Actually, the current implementation does not enforce the declared - number of dimensions either. Arrays of a particular element type are - all considered to be of the same type, regardless of size or number - of dimensions. So, declaring number of dimensions or sizes in - <command>CREATE TABLE</command> is simply documentation, it does not - affect runtime behavior. - </para> - - <para> - The current dimensions of any array value can be retrieved with the - <function>array_dims</function> function: - -<programlisting> -SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; - - array_dims ------------- - [1:2][1:1] -(1 row) -</programlisting> - - <function>array_dims</function> produces a <type>text</type> result, - which is convenient for people to read but perhaps not so convenient - for programs. <function>array_upper</function> and <function> - array_lower</function> return the upper/lower bound of the - given array dimension, respectively. - </para> </sect2> <sect2> @@ -423,7 +410,7 @@ SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; <para> To search for a value in an array, you must check each value of the - array. This can be done by hand (if you know the size of the array). + array. This can be done by hand, if you know the size of the array. For example: <programlisting> @@ -434,41 +421,30 @@ SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR </programlisting> However, this quickly becomes tedious for large arrays, and is not - helpful if the size of the array is unknown. Although it is not built - into <productname>PostgreSQL</productname>, - there is an extension available that defines new functions and - operators for iterating over array values. Using this, the above - query could be: - -<programlisting> -SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000; -</programlisting> - - To search the entire array (not just specified slices), you could - use: + helpful if the size of the array is uncertain. An alternative method is + described in <xref linkend="functions-comparisons">. The above + query could be replaced by: <programlisting> -SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000; +SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); </programlisting> In addition, you could find rows where the array had all values - equal to 10 000 with: + equal to 10000 with: <programlisting> -SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; +SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); </programlisting> - To install this optional module, look in the - <filename>contrib/array</filename> directory of the - <productname>PostgreSQL</productname> source distribution. </para> <tip> <para> - Arrays are not sets; using arrays in the manner described in the - previous paragraph is often a sign of database misdesign. The - array field should generally be split off into a separate table. - Tables can obviously be searched easily. + Arrays are not sets; searching for specific array elements + may be a sign of database misdesign. Consider + using a separate table with a row for each item that would be an + array element. This will be easier to search, and is likely to + scale up better to large numbers of elements. </para> </tip> </sect2> @@ -477,7 +453,7 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; <title>Array Input and Output Syntax</title> <para> - The external representation of an array value consists of items that + The external text representation of an array value consists of items that are interpreted according to the I/O conversion rules for the array's element type, plus decoration that indicates the array structure. The decoration consists of curly braces (<literal>{</> and <literal>}</>) @@ -497,95 +473,18 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; </para> <para> - As illustrated earlier in this chapter, arrays may also be represented - using the <command>ARRAY</command> expression syntax. This representation - of an array value consists of items that are interpreted according to the - I/O conversion rules for the array's element type, plus decoration that - indicates the array structure. The decoration consists of the keyword - <command>ARRAY</command> and square brackets (<literal>[</> and - <literal>]</>) around the array values, plus delimiter characters between - adjacent items. The delimiter character is always a comma (<literal>,</>). - When representing multidimensional arrays, the keyword - <command>ARRAY</command> is only necessary for the outer level. For example, - <literal>'{{"hello world", "happy birthday"}}'</literal> could be written as: -<programlisting> -SELECT ARRAY[['hello world', 'happy birthday']]; - array ------------------------------------- - {{"hello world","happy birthday"}} -(1 row) -</programlisting> - or it also could be written as: -<programlisting> -SELECT ARRAY[ARRAY['hello world', 'happy birthday']]; - array ------------------------------------- - {{"hello world","happy birthday"}} -(1 row) -</programlisting> - </para> - - <para> - A final method to represent an array, is through an - <command>ARRAY</command> sub-select expression. For example: -<programlisting> -SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); - ?column? -------------------------------------------------------------- - {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31} -(1 row) -</programlisting> - The sub-select may <emphasis>only</emphasis> return a single column. The - resulting one-dimensional array will have an element for each row in the - sub-select result, with an element type matching that of the sub-select's - target column. - </para> - - <para> - Arrays may be cast from one type to another in similar fashion to other - data types: - -<programlisting> -SELECT ARRAY[1,2,3]::oid[]; - array ---------- - {1,2,3} -(1 row) - -SELECT CAST(ARRAY[1,2,3] AS float8[]); - array ---------- - {1,2,3} -(1 row) -</programlisting> - - </para> - - </sect2> - - <sect2> - <title>Quoting Array Elements</title> - - <para> - As shown above, when writing an array value you may write double + As shown previously, when writing an array value you may write double quotes around any individual array element. You <emphasis>must</> do so if the element value would otherwise confuse the array-value parser. For example, elements containing curly braces, commas (or whatever the delimiter character is), double quotes, backslashes, or leading white space must be double-quoted. To put a double - quote or backslash in an array element value, precede it with a backslash. + quote or backslash in a quoted array element value, precede it with a + backslash. Alternatively, you can use backslash-escaping to protect all data characters that would otherwise be taken as array syntax or ignorable white space. </para> - <note> - <para> - The discussion in the preceding paragraph with respect to double quoting does - not pertain to the <command>ARRAY</command> expression syntax. In that case, - each element is quoted exactly as any other literal value of the element type. - </para> - </note> - <para> The array output routine will put double quotes around element values if they are empty strings or contain curly braces, delimiter characters, @@ -615,6 +514,15 @@ INSERT ... VALUES ('{"\\\\","\\""}'); in the command to get one backslash into the stored array element.) </para> </note> + + <tip> + <para> + The <literal>ARRAY</> constructor syntax is often easier to work with + than the array-literal syntax when writing array values in SQL commands. + In <literal>ARRAY</>, individual element values are written the same way + they would be written when not members of an array. + </para> + </tip> </sect2> </sect1> diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index fd81d447fd0..b8053f94e55 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.121 2003/07/29 00:03:17 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.122 2003/08/09 22:50:21 tgl Exp $ --> <chapter id="datatype"> @@ -2994,6 +2994,10 @@ SELECT * FROM test; </indexterm> <indexterm zone="datatype-pseudo"> + <primary>anyelement</primary> + </indexterm> + + <indexterm zone="datatype-pseudo"> <primary>void</primary> </indexterm> @@ -3053,7 +3057,14 @@ SELECT * FROM test; <row> <entry><type>anyarray</></entry> - <entry>Indicates that a function accepts any array data type.</entry> + <entry>Indicates that a function accepts any array data type + (see <xref linkend="types-polymorphic">).</entry> + </row> + + <row> + <entry><type>anyelement</></entry> + <entry>Indicates that a function accepts any data type + (see <xref linkend="types-polymorphic">).</entry> </row> <row> @@ -3101,8 +3112,10 @@ SELECT * FROM test; Functions coded in procedural languages may use pseudo-types only as allowed by their implementation languages. At present the procedural languages all forbid use of a pseudo-type as argument type, and allow - only <type>void</> as a result type (plus <type>trigger</> when the - function is used as a trigger). + only <type>void</> and <type>record</> as a result type (plus + <type>trigger</> when the function is used as a trigger). Some also + support polymorphic functions using the types <type>anyarray</> and + <type>anyelement</>. </para> <para> diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index f9adeee281b..7d0f65f0679 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.23 2003/08/09 22:50:21 tgl Exp $ --> <chapter id="extend"> @@ -22,6 +22,11 @@ $Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 pete </listitem> <listitem> <para> + aggregates (starting in <xref linkend="xaggr">) + </para> + </listitem> + <listitem> + <para> data types (starting in <xref linkend="xtypes">) </para> </listitem> @@ -32,7 +37,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 pete </listitem> <listitem> <para> - aggregates (starting in <xref linkend="xaggr">) + operator classes for indexes (starting in <xref linkend="xindex">) </para> </listitem> </itemizedlist> @@ -47,7 +52,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 pete 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 + this the data dictionary.) The catalogs appear to the user as tables like any other, but the <acronym>DBMS</acronym> stores its internal bookkeeping in them. One key difference between <productname>PostgreSQL</productname> and standard relational database systems is @@ -88,24 +93,113 @@ $Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 pete </indexterm> <para> - Data types are divided into base types and composite types. + <productname>PostgreSQL</productname> data types are divided into base + types, composite types, domain types, and pseudo-types. + </para> + + <para> Base types are those, like <type>int4</type>, that are implemented - in a language such as C. They generally correspond to - what are often known as abstract data types. <productname>PostgreSQL</productname> - can only operate on such types through methods provided + below the level of the <acronym>SQL</> language (typically in a low-level + language such as C). They generally correspond to + what are often known as abstract data types. + <productname>PostgreSQL</productname> + can only operate on such types through functions provided by the user and only understands the behavior of such - types to the extent that the user describes them. - Composite types are created whenever the user creates a - table. The - user can <quote>look inside</quote> at the attributes of these types - from the query language. + types to the extent that the user describes them. Base types are + further subdivided into scalar and array types. For each scalar type, + a corresponding array type is automatically created that can hold + variable-size arrays of that scalar type. + </para> + + <para> + Composite types, or row types, are created whenever the user creates a + table; it's also possible to define a <quote>stand-alone</> composite + type with no associated table. A composite type is simply a list of + base types with associated field names. A value of a composite type + is a row or record of field values. The user can access the component + fields from <acronym>SQL</> queries. + </para> + + <para> + A domain type is based on a particular base + type and for many purposes is interchangeable with its base type. + However, a domain may have constraints that restrict its valid values + to a subset of what the underlying base type would allow. Domains can + be created by simple <acronym>SQL</> commands. </para> + + <para> + Finally, there are a few <quote>pseudo-types</> for special purposes. + Pseudo-types cannot appear as fields of tables or composite types, but + they can be used to declare the argument and result types of functions. + This provides a mechanism within the type system to identify special + classes of functions. <xref + linkend="datatype-pseudotypes-table"> lists the existing + pseudo-types. + </para> + + <sect2 id="types-polymorphic"> + <title>Polymorphic Types and Functions</title> + + <indexterm> + <primary>polymorphic types</primary> + </indexterm> + + <indexterm> + <primary>polymorphic functions</primary> + </indexterm> + + <para> + Two pseudo-types of special interest are <type>anyelement</> and + <type>anyarray</>, which are collectively called <firstterm>polymorphic + types</>. Any function declared using these types is said to be + a <firstterm>polymorphic function</>. A polymorphic function can + operate on many different data types, with the specific data type(s) + being determined by the data types actually passed to it in a particular + call. + </para> + + <para> + Polymorphic arguments and results are tied to each other and are resolved + to a specific data type when a query calling a polymorphic function is + parsed. Each position (either argument or return value) declared as + <type>anyelement</type> is allowed to have any specific actual + data type, but in any given call they must all be the + <emphasis>same</emphasis> actual type. Each + position declared as <type>anyarray</type> can have any array data type, + but similarly they must all be the same type. If there are + positions declared <type>anyarray</type> and others declared + <type>anyelement</type>, the actual array type in the + <type>anyarray</type> positions must be an array whose elements are + the same type appearing in the <type>anyelement</type> positions. + </para> + + <para> + Thus, when more than one argument position is declared with a polymorphic + type, the net effect is that only certain combinations of actual argument + types are allowed. For example, a function declared as + <literal>foo(anyelement, anyelement)</> will take any two input values, + so long as they are of the same data type. + </para> + + <para> + When the return value of a function is declared as a polymorphic type, + there must be at least one argument position that is also polymorphic, + and the actual data type supplied as the argument determines the actual + result type for that call. For example, if there were not already + an array subscripting mechanism, one could define a function that + implements subscripting as <literal>subscript(anyarray, integer) + returns anyelement</>. This declaration constrains the actual first + argument to be an array type, and allows the parser to infer the correct + result type from the actual first argument's type. + </para> + </sect2> </sect1> &xfunc; + &xaggr; &xtypes; &xoper; - &xaggr; &xindex; </chapter> diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f06f289d2d9..d9cb8bb018e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.164 2003/08/04 14:00:13 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.165 2003/08/09 22:50:21 tgl Exp $ PostgreSQL documentation --> @@ -7044,28 +7044,67 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); <tbody> <row> <entry> <literal>=</literal> </entry> - <entry>equals</entry> + <entry>equal</entry> <entry><literal>ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3]</literal></entry> <entry><literal>t</literal></entry> </row> + + <row> + <entry> <literal><></literal> </entry> + <entry>not equal</entry> + <entry><literal>ARRAY[1,2,3] <> ARRAY[1,2,4]</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><</literal> </entry> + <entry>less than</entry> + <entry><literal>ARRAY[1,2,3] < ARRAY[1,2,4]</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>></literal> </entry> + <entry>greater than</entry> + <entry><literal>ARRAY[1,4,3] > ARRAY[1,2,4]</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal><=</literal> </entry> + <entry>less than or equal</entry> + <entry><literal>ARRAY[1,2,3] <= ARRAY[1,2,3]</literal></entry> + <entry><literal>t</literal></entry> + </row> + + <row> + <entry> <literal>>=</literal> </entry> + <entry>greater than or equal</entry> + <entry><literal>ARRAY[1,4,3] >= ARRAY[1,4,3]</literal></entry> + <entry><literal>t</literal></entry> + </row> + <row> <entry> <literal>||</literal> </entry> <entry>array-to-array concatenation</entry> <entry><literal>ARRAY[1,2,3] || ARRAY[4,5,6]</literal></entry> <entry><literal>{{1,2,3},{4,5,6}}</literal></entry> </row> + <row> <entry> <literal>||</literal> </entry> <entry>array-to-array concatenation</entry> <entry><literal>ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]]</literal></entry> <entry><literal>{{1,2,3},{4,5,6},{7,8,9}}</literal></entry> </row> + <row> <entry> <literal>||</literal> </entry> <entry>element-to-array concatenation</entry> <entry><literal>3 || ARRAY[4,5,6]</literal></entry> <entry><literal>{3,4,5,6}</literal></entry> </row> + <row> <entry> <literal>||</literal> </entry> <entry>array-to-element concatenation</entry> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 2f51d50cf3e..b313860ea0c 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.19 2003/05/28 16:03:55 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v 1.20 2003/08/09 22:50:22 tgl Exp $ --> <chapter id="plpgsql"> @@ -177,16 +177,54 @@ END; </para> </sect2> - <sect2 id="plpgsql-overview-developing-in-plpgsql"> - <title>Developing in <application>PL/pgSQL</application></title> + <sect2 id="plpgsql-args-results"> + <title>Supported Argument and Result Datatypes</title> + + <para> + Functions written in <application>PL/pgSQL</application> can accept + as arguments any scalar or array datatype supported by the server, + and they can return a result of any of these types. They can also + accept or return any composite type (row type) specified by name. + It is also possible to declare a <application>PL/pgSQL</application> + function as returning <type>record</>, which means that the result + is a row type whose columns are determined by specification in the + calling query, as discussed in <xref linkend="queries-tablefunctions">. + </para> + + <para> + <application>PL/pgSQL</> functions may also be declared to accept + and return the <quote>polymorphic</> types + <type>anyelement</type> and <type>anyarray</type>. The actual + datatypes handled by a polymorphic function can vary from call to + call, as discussed in <xref linkend="types-polymorphic">. + An example is shown in <xref linkend="plpgsql-declaration-aliases">. + </para> + + <para> + <application>PL/pgSQL</> functions can also be declared to return + a <quote>set</>, or table, of any datatype they can return a single + instance of. Such a function generates its output by executing + <literal>RETURN NEXT</> for each desired element of the result set. + </para> + + <para> + Finally, a <application>PL/pgSQL</> function may be declared to return + <type>void</> if it has no useful return value. + </para> + </sect2> + </sect1> + + <sect1 id="plpgsql-development-tips"> + <title>Tips for Developing in <application>PL/pgSQL</application></title> <para> One good way to develop in - <application>PL/pgSQL</> is to simply use the text editor of your + <application>PL/pgSQL</> is to use the text editor of your choice to create your functions, and in another window, use - <command>psql</command> to load those functions. If you are doing it this way, it + <application>psql</application> to load and test those functions. + If you are doing it this way, it is a good idea to write the function using <command>CREATE OR - REPLACE FUNCTION</>. That way you can reload the file to update + REPLACE FUNCTION</>. That way you can just reload the file to update the function definition. For example: <programlisting> CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS ' @@ -197,8 +235,8 @@ end; </para> <para> - While running <command>psql</command>, you can load or reload such a - function definition file with + While running <application>psql</application>, you can load or reload such + a function definition file with <programlisting> \i filename.sql </programlisting> @@ -213,39 +251,40 @@ end; provide convenient features such as escaping single quotes and making it easier to recreate and debug functions. </para> - </sect2> - </sect1> - <sect1 id="plpgsql-quote"> - <title>Handling of Quotations Marks</title> + <sect2 id="plpgsql-quote-tips"> + <title>Handling of Quote Marks</title> <para> - Since the code of any procedural language function is specified + Since the code of a <application>PL/pgSQL</> function is specified in <command>CREATE FUNCTION</command> as a string literal, single - quotes inside the function body must be escaped. This can lead to + quotes inside the function body must be escaped by doubling them. + This can lead to rather complicated code at times, especially if you are writing a function that generates other functions, as in the example in <xref - linkend="plpgsql-statements-executing-dyn">. The list below gives - you an overview over the needed levels of quotation marks in - various situations. Keep this chart handy. + linkend="plpgsql-statements-executing-dyn">. This chart may be useful + as a summary of the needed numbers of quote marks in + various situations. </para> <variablelist> <varlistentry> - <term>1 quotation mark</term> + <term>1 quote mark</term> <listitem> <para> - To begin/end function bodies, for example: + To begin and end the function body, for example: <programlisting> CREATE FUNCTION foo() RETURNS integer AS '...' LANGUAGE plpgsql; </programlisting> + Anywhere within the function body, quote marks <emphasis>must</> + appear in pairs. </para> </listitem> </varlistentry> <varlistentry> - <term>2 quotation marks</term> + <term>2 quote marks</term> <listitem> <para> For string literals inside the function body, for example: @@ -253,7 +292,7 @@ CREATE FUNCTION foo() RETURNS integer AS '...' a_output := ''Blah''; SELECT * FROM users WHERE f_name=''foobar''; </programlisting> - The second line is interpreted as + The second line is seen by <application>PL/pgSQL</> as <programlisting> SELECT * FROM users WHERE f_name='foobar'; </programlisting> @@ -262,22 +301,22 @@ SELECT * FROM users WHERE f_name='foobar'; </varlistentry> <varlistentry> - <term>4 quotation marks</term> + <term>4 quote marks</term> <listitem> <para> - When you need a single quote in a string inside the function + When you need a single quote in a string constant inside the function body, for example: <programlisting> a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz'' </programlisting> - The value of <literal>a_output</literal> would then be: <literal> - AND name LIKE 'foobar' AND xyz</literal>. + The value actually appended to <literal>a_output</literal> would be: + <literal> AND name LIKE 'foobar' AND xyz</literal>. </para> </listitem> </varlistentry> <varlistentry> - <term>6 quotation marks</term> + <term>6 quote marks</term> <listitem> <para> When a single quote in a string inside the function body is @@ -285,14 +324,14 @@ a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz'' <programlisting> a_output := a_output || '' AND name LIKE ''''foobar'''''' </programlisting> - The value of <literal>a_output</literal> would then be: + The value appended to <literal>a_output</literal> would then be: <literal> AND name LIKE 'foobar'</literal>. </para> </listitem> </varlistentry> <varlistentry> - <term>10 quotation marks</term> + <term>10 quote marks</term> <listitem> <para> When you want two single quotes in a string constant (which @@ -315,6 +354,15 @@ if v_... like ''...'' then return ''...''; end if; </listitem> </varlistentry> </variablelist> + + <para> + A different approach is to escape quote marks in the function body + with a backslash rather than by doubling them. With this method + you'll find yourself writing things like <literal>\'\'</> instead + of <literal>''''</>. Some find this easier to keep track of, some + do not. + </para> + </sect2> </sect1> <sect1 id="plpgsql-structure"> @@ -474,7 +522,8 @@ user_id CONSTANT integer := 10; <para> Parameters passed to functions are named with the identifiers <literal>$1</literal>, <literal>$2</literal>, - etc. Optionally, aliases can be declared for <literal>$<replaceable>n</replaceable></literal> + etc. Optionally, aliases can be declared for + <literal>$<replaceable>n</replaceable></literal> parameter names for increased readability. Either the alias or the numeric identifier can then be used to refer to the parameter value. Some examples: @@ -498,7 +547,7 @@ END; ' LANGUAGE plpgsql; -CREATE FUNCTION use_many_fields(tablename) RETURNS text AS ' +CREATE FUNCTION concat_selected_fields(tablename) RETURNS text AS ' DECLARE in_t ALIAS FOR $1; BEGIN @@ -507,6 +556,36 @@ END; ' LANGUAGE plpgsql; </programlisting> </para> + + <para> + When the return type of a <application>PL/pgSQL</application> + function is declared as a polymorphic type (<type>anyelement</type> + or <type>anyarray</type>), a special parameter <literal>$0</literal> + is created. Its datatype is the actual return type of the function, + as deduced from the actual input types (see <xref + linkend="types-polymorphic">). + This allows the function to access its actual return type + as shown in <xref linkend="plpgsql-declaration-type">. + <literal>$0</literal> is initialized to NULL and can be modified by + the function, so it can be used to hold the return value if desired, + though that is not required. <literal>$0</literal> can also be + given an alias. For example, this function works on any datatype + that has a <literal>+</> operator: +<programlisting> +CREATE FUNCTION add_three_values(anyelement, anyelement, anyelement) +RETURNS anyelement AS ' +DECLARE + result ALIAS FOR $0; + first ALIAS FOR $1; + second ALIAS FOR $2; + third ALIAS FOR $3; +BEGIN + result := first + second + third; + RETURN result; +END; +' LANGUAGE plpgsql; +</programlisting> + </para> </sect2> <sect2 id="plpgsql-declaration-type"> @@ -536,6 +615,15 @@ user_id users.user_id%TYPE; from <type>integer</type> to <type>real</type>), you may not need to change your function definition. </para> + + <para> + <literal>%TYPE</literal> is particularly valuable in polymorphic + functions, since the data types needed for internal variables may + change from one call to the next. Appropriate variables can be + created by applying <literal>%TYPE</literal> to the function's + arguments or result placeholders. + </para> + </sect2> <sect2 id="plpgsql-declaration-rowtypes"> @@ -620,6 +708,14 @@ END; <para> Note that <literal>RECORD</> is not a true data type, only a placeholder. + One should also realize that when a <application>PL/pgSQL</application> + function is declared to return type <type>record</>, this is not quite the + same concept as a record variable, even though such a function may well + use a record variable to hold its result. In both cases the actual row + structure is unknown when the function is written, but for a function + returning <type>record</> the actual structure is determined when the + calling query is parsed, whereas a record variable can change its row + structure on-the-fly. </para> </sect2> @@ -965,7 +1061,7 @@ EXECUTE <replaceable class="command">command-string</replaceable>; <para> When working with dynamic commands you will have to face escaping of single quotes in <application>PL/pgSQL</>. Please refer to the - overview in <xref linkend="plpgsql-quote">, + overview in <xref linkend="plpgsql-quote-tips">, which can save you some effort. </para> @@ -1004,11 +1100,11 @@ EXECUTE ''UPDATE tbl SET '' <function>quote_literal(<type>text</type>)</function>. Variables containing column and table identifiers should be passed to function <function>quote_ident</function>. - Variables containing values that act as value literals in the constructed command - string should be passed to + Variables containing values that should be literal strings in the + constructed command should be passed to <function>quote_literal</function>. Both take the - appropriate steps to return the input text enclosed in single - or double quotes and with any embedded special characters + appropriate steps to return the input text enclosed in double + or single quotes respectively, with any embedded special characters properly escaped. </para> @@ -2253,7 +2349,7 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp <listitem> <para> In <productname>PostgreSQL</> you need to escape single - quotes in the function body. See <xref linkend="plpgsql-quote">. + quotes in the function body. See <xref linkend="plpgsql-quote-tips">. </para> </listitem> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 8d23a3e3548..c77456a0e88 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.80 2003/08/04 14:00:14 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/syntax.sgml,v 1.81 2003/08/09 22:50:22 tgl Exp $ --> <chapter id="sql-syntax"> @@ -867,7 +867,8 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; <listitem> <para> - A positional parameter reference, in the body of a function definition. + A positional parameter reference, in the body of a function definition + or prepared statement. </para> </listitem> @@ -903,6 +904,12 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; <listitem> <para> + An array constructor. + </para> + </listitem> + + <listitem> + <para> Another value expression in parentheses, useful to group subexpressions and override precedence. </para> </listitem> @@ -1216,8 +1223,86 @@ SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) </para> </sect2> + <sect2 id="sql-syntax-array-constructors"> + <title>Array Constructors</title> + + <indexterm> + <primary>arrays</primary> + <secondary>constructors</secondary> + </indexterm> + + <para> + An <firstterm>array constructor</> is an expression that builds an + array value from values for its member elements. A simple array + constructor + consists of the keyword <literal>ARRAY</literal>, a left square bracket + <literal>[</>, one or more expressions (separated by commas) for the + array element values, and finally a right square bracket <literal>]</>. + For example, +<programlisting> +SELECT ARRAY[1,2,3+4]; + array +--------- + {1,2,7} +(1 row) +</programlisting> + The array element type is the common type of the member expressions, + determined using the same rules as for <literal>UNION</> or + <literal>CASE</> constructs (see <xref linkend="typeconv-union-case">). + </para> + + <para> + Multidimensional array values can be built by nesting array + constructors. + In the inner constructors, the keyword <literal>ARRAY</literal> may + be omitted. For example, these produce the same result: + +<programlisting> +SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; + array +--------------- + {{1,2},{3,4}} +(1 row) + +SELECT ARRAY[[1,2],[3,4]]; + array +--------------- + {{1,2},{3,4}} +(1 row) +</programlisting> + + Since multidimensional arrays must be rectangular, inner constructors + at the same level must produce sub-arrays of identical dimensions. + </para> + + <para> + It is also possible to construct an array from the results of a + subquery. In this form, the array constructor is written with the + keyword <literal>ARRAY</literal> followed by a parenthesized (not + bracketed) subquery. For example: +<programlisting> +SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); + ?column? +------------------------------------------------------------- + {2011,1954,1948,1952,1951,1244,1950,2005,1949,1953,2006,31} +(1 row) +</programlisting> + The sub-select must return a single column. The + resulting one-dimensional array will have an element for each row in the + sub-select result, with an element type matching that of the sub-select's + output column. + </para> + + <para> + The subscripts of an array value built with <literal>ARRAY</literal> + always begin with one. For more information about arrays, see + <xref linkend="arrays">. + </para> + + </sect2> + <sect2 id="syntax-express-eval"> - <title>Expression Evaluation</title> + <title>Expression Evaluation Rules</title> <para> The order of evaluation of subexpressions is not defined. In diff --git a/doc/src/sgml/xaggr.sgml b/doc/src/sgml/xaggr.sgml index 101067e1756..7b82af322ea 100644 --- a/doc/src/sgml/xaggr.sgml +++ b/doc/src/sgml/xaggr.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/xaggr.sgml,v 1.20 2003/04/10 01:22:44 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/xaggr.sgml,v 1.21 2003/08/09 22:50:22 tgl Exp $ --> <sect1 id="xaggr"> @@ -72,8 +72,9 @@ SELECT complex_sum(a) FROM test_complex; omitting the <literal>initcond</literal> phrase, so that the initial state condition is null. Ordinarily this would mean that the <literal>sfunc</literal> would need to check for a null state-condition input, but for - <function>sum</function> and some other simple aggregates like <function>max</> and <function>min</>, - it would be sufficient to insert the first nonnull input value into + <function>sum</function> and some other simple aggregates like + <function>max</> and <function>min</>, + it is sufficient to insert the first nonnull input value into the state variable and then start applying the transition function at the second nonnull input value. <productname>PostgreSQL</productname> will do that automatically if the initial condition is null and @@ -111,8 +112,55 @@ CREATE AGGREGATE avg ( </para> <para> - For further details see the description of the <command>CREATE - AGGREGATE</command> command in <xref linkend="reference">. + Aggregate functions may use polymorphic + state transition functions or final functions, so that the same functions + can be used to implement multiple aggregates. + See <xref linkend="types-polymorphic"> + for an explanation of polymorphic functions. + Going a step further, the aggregate function itself may be specified + with a polymorphic base type and state type, allowing a single + aggregate definition to serve for multiple input data types. + Here is an example of a polymorphic aggregate: + +<programlisting> +CREATE AGGREGATE array_accum ( + sfunc = array_append, + basetype = anyelement, + stype = anyarray, + initcond = '{}' +); +</programlisting> + + Here, the actual state type for any aggregate call is the array type + having the actual input type as elements. + </para> + + <para> + Here's the output using two different actual data types as arguments: + +<programlisting> +SELECT attrelid::regclass, array_accum(attname) +FROM pg_attribute WHERE attnum > 0 +AND attrelid = 'pg_user'::regclass GROUP BY attrelid; + attrelid | array_accum +----------+----------------------------------------------------------------------------- + pg_user | {usename,usesysid,usecreatedb,usesuper,usecatupd,passwd,valuntil,useconfig} +(1 row) + +SELECT attrelid::regclass, array_accum(atttypid) +FROM pg_attribute WHERE attnum > 0 +AND attrelid = 'pg_user'::regclass GROUP BY attrelid; + attrelid | array_accum +----------+------------------------------ + pg_user | {19,23,16,16,16,25,702,1009} +(1 row) +</programlisting> + </para> + + <para> + For further details see the + <xref linkend="sql-createaggregate" endterm="sql-createaggregate-title"> + command. </para> </sect1> diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 71c9ec1ce7b..25997718deb 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.70 2003/07/25 20:17:49 tgl Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.71 2003/08/09 22:50:22 tgl Exp $ --> <sect1 id="xfunc"> @@ -41,22 +41,29 @@ $Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.70 2003/07/25 20:17:49 tgl E <para> Every kind of function can take base types, composite types, or - some combination as arguments (parameters). In addition, + combinations of these as arguments (parameters). In addition, every kind of function can return a base type or a composite type. </para> <para> + Many kinds of functions can take or return certain pseudo-types + (such as polymorphic types), but the available facilities vary. + Consult the description of each kind of function for more details. + </para> + + <para> It's easiest to define <acronym>SQL</acronym> - functions, so we'll start with those. Examples in this section - can also be found in <filename>funcs.sql</filename> - and <filename>funcs.c</filename> in the tutorial directory. + functions, so we'll start by discussing those. </para> <para> Throughout this chapter, it can be useful to look at the reference page of the <command>CREATE FUNCTION</command> command to understand the examples better. + Some examples from this chapter + can be found in <filename>funcs.sql</filename> + and <filename>funcs.c</filename> in the tutorial directory. </para> </sect1> @@ -67,8 +74,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.70 2003/07/25 20:17:49 tgl E <para> SQL functions execute an arbitrary list of SQL statements, returning - the result of the last query in the list, which must be a - <literal>SELECT</>. + the result of the last query in the list. In the simple (non-set) case, the first row of the last query's result will be returned. (Bear in mind that <quote>the first row</quote> of a multirow @@ -276,7 +282,7 @@ CREATE FUNCTION new_emp() RETURNS emp AS ' ' LANGUAGE SQL; </programlisting> - In this case we have specified each of the attributes + In this example we have specified each of the attributes with a constant value, but any computation could have been substituted for these constants. </para> @@ -316,7 +322,7 @@ ERROR: function declared to return emp returns varchar instead of text at colum </para> <para> - This is an example for how to extract an attribute out of a row type: + This is an example of extracting an attribute out of a row type: <screen> SELECT (new_emp()).name; @@ -330,7 +336,7 @@ SELECT (new_emp()).name; <screen> SELECT new_emp().name; -ERROR: syntax error at or near "." +ERROR: syntax error at or near "." at character 17 </screen> </para> @@ -509,6 +515,68 @@ SELECT name, listchildren(name) FROM nodes; for those arguments, so no result rows are generated. </para> </sect2> + + <sect2> + <title>Polymorphic <acronym>SQL</acronym> Functions</title> + + <para> + <acronym>SQL</acronym> functions may be declared to accept and + return the <quote>polymorphic</> types + <type>anyelement</type> and <type>anyarray</type>. + See <xref linkend="types-polymorphic"> for a more detailed explanation + of polymorphic functions. Here is a polymorphic function + <function>make_array</function> that builds up an array from two + arbitrary data type elements: +<screen> +CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS ' + SELECT ARRAY[$1, $2]; +' LANGUAGE SQL; + +SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray; + intarray | textarray +----------+----------- + {1,2} | {a,b} +(1 row) +</screen> + </para> + + <para> + Notice the use of the typecast <literal>'a'::text</literal> + to specify that the argument is of type <type>text</type>. This is + required if the argument is just a string literal, since otherwise + it would be treated as type + <type>unknown</type>, and array of <type>unknown</type> is not a valid + type. + Without the typecast, you will get errors like this: +<screen> +<computeroutput> +ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN +</computeroutput> +</screen> + </para> + + <para> + It is permitted to have polymorphic arguments with a deterministic + return type, but the converse is not. For example: +<screen> +CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS bool AS ' + SELECT $1 > $2; +' LANGUAGE SQL; + +SELECT is_greater(1, 2); + is_greater +------------ + f +(1 row) + +CREATE FUNCTION invalid_func() RETURNS anyelement AS ' + SELECT 1; +' LANGUAGE SQL; +ERROR: cannot determine result datatype +DETAIL: A function returning ANYARRAY or ANYELEMENT must have at least one argument of either type. +</screen> + </para> + </sect2> </sect1> <sect1 id="xfunc-pl"> @@ -1999,6 +2067,89 @@ CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testp distribution contains more examples of set-returning functions. </para> </sect2> + + <sect2> + <title>Polymorphic Arguments and Return Types</title> + + <para> + C-language functions may be declared to accept and + return the <quote>polymorphic</> types + <type>anyelement</type> and <type>anyarray</type>. + See <xref linkend="types-polymorphic"> for a more detailed explanation + of polymorphic functions. When function arguments or return types + are defined as polymorphic types, the function author cannot know + in advance what data type it will be called with, or + need to return. There are two routines provided in <filename>fmgr.h</> + to allow a version-1 C function to discover the actual data types + of its arguments and the type it is expected to return. The routines are + called <literal>get_fn_expr_rettype(FmgrInfo *flinfo)</> and + <literal>get_fn_expr_argtype(FmgrInfo *flinfo, int argnum)</>. + They return the result or argument type OID, or InvalidOid if the + information is not available. + The structure <literal>flinfo</> is normally accessed as + <literal>fcinfo->flinfo</>. The parameter <literal>argnum</> + is zero based. + </para> + + <para> + For example, suppose we want to write a function to accept a single + element of any type, and return a one-dimensional array of that type: + +<programlisting> +PG_FUNCTION_INFO_V1(make_array); +Datum +make_array(PG_FUNCTION_ARGS) +{ + ArrayType *result; + Oid element_type = get_fn_expr_argtype(fcinfo->flinfo, 0); + Datum element; + int16 typlen; + bool typbyval; + char typalign; + int ndims; + int dims[MAXDIM]; + int lbs[MAXDIM]; + + if (!OidIsValid(element_type)) + elog(ERROR, "could not determine data type of input"); + + /* get the provided element */ + element = PG_GETARG_DATUM(0); + + /* we have one dimension */ + ndims = 1; + /* and one element */ + dims[0] = 1; + /* and lower bound is 1 */ + lbs[0] = 1; + + /* get required info about the element type */ + get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign); + + /* now build the array */ + result = construct_md_array(&element, ndims, dims, lbs, + element_type, typlen, typbyval, typalign); + + PG_RETURN_ARRAYTYPE_P(result); +} +</programlisting> + </para> + + <para> + The following command declares the function + <function>make_array</function> in SQL: + +<programlisting> +CREATE FUNCTION make_array(anyelement) + RETURNS anyarray + AS '<replaceable>DIRECTORY</replaceable>/funcs', 'make_array' + LANGUAGE 'C' STRICT; +</programlisting> + + Note the use of STRICT; this is essential since the code is not + bothering to test for a NULL input. + </para> + </sect2> </sect1> <sect1 id="xfunc-overload"> |