From 5bfb0540b0e3f3285b78cff610758c45d8269ba5 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 9 Aug 2003 22:50:22 +0000 Subject: [PATCH] Update docs for 7.4 array features and polymorphic functions. This is Joe Conway's patch of 7-Aug plus further editorializing of my own. --- doc/src/sgml/array.sgml | 338 +++++++++++++++++---------------------------- doc/src/sgml/datatype.sgml | 21 ++- doc/src/sgml/extend.sgml | 120 ++++++++++++++-- doc/src/sgml/func.sgml | 43 +++++- doc/src/sgml/plpgsql.sgml | 168 +++++++++++++++++----- doc/src/sgml/syntax.sgml | 91 +++++++++++- doc/src/sgml/xaggr.sgml | 58 +++++++- 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 a0c93aedeb..fabf2e732c 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ - + Arrays @@ -36,6 +36,41 @@ CREATE TABLE sal_emp ( text (schedule), which represents the employee's weekly schedule. + + + The syntax for CREATE TABLE allows the exact size of + arrays to be specified, for example: + + +CREATE TABLE tictactoe ( + squares integer[3][3] +); + + + However, the current implementation does not enforce the array size + limits --- the behavior is the same as for arrays of unspecified + length. + + + + 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 + CREATE TABLE is simply documentation, it does not + affect runtime behavior. + + + + An alternative, SQL99-standard syntax may be used for one-dimensional arrays. + pay_by_quarter could have been defined as: + + pay_by_quarter integer ARRAY[4], + + This syntax requires an integer constant to denote the array size. + As before, however, PostgreSQL does not enforce the + size restriction. + @@ -43,9 +78,11 @@ CREATE TABLE sal_emp ( Now we can show some INSERT 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.) INSERT INTO sal_emp @@ -90,7 +127,7 @@ SELECT * FROM sal_emp; - The ARRAY expression syntax may also be used: + The ARRAY expression syntax may also be used: INSERT INTO sal_emp VALUES ('Bill', @@ -109,29 +146,27 @@ SELECT * FROM sal_emp; (2 rows) 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: 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 - 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 ARRAY + expression syntax is discussed in more detail in . - - - The examples in the rest of this section are based on the - ARRAY expression syntax INSERTs. - - - - Array Value References + Accessing Arrays 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 lower:upper. 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: 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'; - Additionally, we can also access a single arbitrary array element of - a one-dimensional array with the array_subscript - function: + The current dimensions of any array value can be retrieved with the + array_dims function: + -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) + + + array_dims produces a text result, + which is convenient for people to read but perhaps not so convenient + for programs. Dimensions can also be retrieved with + array_upper and array_lower, + which return the upper and lower bound of a + specified array dimension, respectively. + + +SELECT array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol'; + + array_upper +------------- + 2 (1 row) + + + + Modifying Arrays 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'; - or using the ARRAY expression syntax: + or using the ARRAY expression syntax: UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; - - - Anywhere you can use the curly braces array syntax, - you can also use the ARRAY expression syntax. The - remainder of this section will illustrate only one or the other, but - not both. - - - An array may also be updated at a single element: @@ -256,34 +303,27 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; - A one-dimensional array may also be updated with the - array_assign function: - - -UPDATE sal_emp SET pay_by_quarter = array_assign(pay_by_quarter, 4, 15000) - WHERE name = 'Bill'; - - 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 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 + myarray currently has 4 elements, it will have five + elements after an update that assigns to myarray[5]. + Currently, enlargement in this fashion is only allowed for one-dimensional + arrays, not multidimensional arrays. Array slice assignment allows creation of arrays that do not use one-based - subscripts. For example one might assign to array[-2:7] to + subscripts. For example one might assign to myarray[-2:7] to create an array with subscript values running from -2 to 7. - An array can also be enlarged by using the concatenation operator, - ||. + New array values can also be constructed by using the concatenation operator, + ||. SELECT ARRAY[1,2] || ARRAY[3,4]; ?column? @@ -299,7 +339,7 @@ SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; 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 N-dimensional arrays, or an N-dimensional and an N+1-dimensional array. In the former case, the two N-dimension arrays become outer elements of an @@ -307,12 +347,13 @@ SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]]; N-dimensional array is added as either the first or last outer element of the 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: -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; - An array can also be enlarged by using the functions + An array can also be constructed by using the functions array_prepend, array_append, or array_cat. The first two only support one-dimensional arrays, but array_cat supports multidimensional arrays. @@ -362,60 +403,6 @@ SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]); {{5,6},{1,2},{3,4}} - - - The syntax for CREATE TABLE allows fixed-length - arrays to be defined: - - -CREATE TABLE tictactoe ( - squares integer[3][3] -); - - - However, the current implementation does not enforce the array size - limits --- the behavior is the same as for arrays of unspecified - length. - - - - An alternative syntax for one-dimensional arrays may be used. - pay_by_quarter could have been defined as: - - pay_by_quarter integer ARRAY[4], - - This syntax may only be used with the integer - constant to denote the array size. - - - - 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 - CREATE TABLE is simply documentation, it does not - affect runtime behavior. - - - - The current dimensions of any array value can be retrieved with the - array_dims function: - - -SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; - - array_dims ------------- - [1:2][1:1] -(1 row) - - - array_dims produces a text result, - which is convenient for people to read but perhaps not so convenient - for programs. array_upper and - array_lower return the upper/lower bound of the - given array dimension, respectively. - @@ -423,7 +410,7 @@ SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; 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: @@ -434,41 +421,30 @@ SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR 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 PostgreSQL, - there is an extension available that defines new functions and - operators for iterating over array values. Using this, the above - query could be: - - -SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000; - - - 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 . The above + query could be replaced by: -SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000; +SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); In addition, you could find rows where the array had all values - equal to 10 000 with: + equal to 10000 with: -SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; +SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); - To install this optional module, look in the - contrib/array directory of the - PostgreSQL source distribution. - 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. @@ -477,7 +453,7 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; Array Input and Output Syntax - 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 ({ and }) @@ -497,95 +473,18 @@ SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; - As illustrated earlier in this chapter, arrays may also be represented - using the ARRAY 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 - ARRAY and square brackets ([ and - ]) around the array values, plus delimiter characters between - adjacent items. The delimiter character is always a comma (,). - When representing multidimensional arrays, the keyword - ARRAY is only necessary for the outer level. For example, - '{{"hello world", "happy birthday"}}' could be written as: - -SELECT ARRAY[['hello world', 'happy birthday']]; - array ------------------------------------- - {{"hello world","happy birthday"}} -(1 row) - - or it also could be written as: - -SELECT ARRAY[ARRAY['hello world', 'happy birthday']]; - array ------------------------------------- - {{"hello world","happy birthday"}} -(1 row) - - - - - A final method to represent an array, is through an - ARRAY sub-select expression. For example: - -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) - - The sub-select may only 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. - - - - Arrays may be cast from one type to another in similar fashion to other - data types: - - -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) - - - - - - - - Quoting Array Elements - - - 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 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. - - - The discussion in the preceding paragraph with respect to double quoting does - not pertain to the ARRAY expression syntax. In that case, - each element is quoted exactly as any other literal value of the element type. - - - 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.) + + + + The ARRAY constructor syntax is often easier to work with + than the array-literal syntax when writing array values in SQL commands. + In ARRAY, individual element values are written the same way + they would be written when not members of an array. + + diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index fd81d447fd..b8053f94e5 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -2994,6 +2994,10 @@ SELECT * FROM test; + anyelement + + + void @@ -3053,7 +3057,14 @@ SELECT * FROM test; anyarray - Indicates that a function accepts any array data type. + Indicates that a function accepts any array data type + (see ). + + + + anyelement + Indicates that a function accepts any data type + (see ). @@ -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 void as a result type (plus trigger when the - function is used as a trigger). + only void and record as a result type (plus + trigger when the function is used as a trigger). Some also + support polymorphic functions using the types anyarray and + anyelement. diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index f9adeee281..7d0f65f067 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -1,5 +1,5 @@ @@ -22,6 +22,11 @@ $Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 pete + aggregates (starting in ) + + + + data types (starting in ) @@ -32,7 +37,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/extend.sgml,v 1.22 2003/04/13 09:57:35 pete - aggregates (starting in ) + operator classes for indexes (starting in ) @@ -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 DBMS stores its internal bookkeeping in them. One key difference between PostgreSQL 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 - Data types are divided into base types and composite types. + PostgreSQL data types are divided into base + types, composite types, domain types, and pseudo-types. + + + Base types are those, like int4, that are implemented - in a language such as C. They generally correspond to - what are often known as abstract data types. PostgreSQL - can only operate on such types through methods provided + below the level of the SQL language (typically in a low-level + language such as C). They generally correspond to + what are often known as abstract data types. + PostgreSQL + 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 look inside 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. + + + + Composite types, or row types, are created whenever the user creates a + table; it's also possible to define a 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 SQL queries. + + + + 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 SQL commands. + + + Finally, there are a few 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. lists the existing + pseudo-types. + + + + Polymorphic Types and Functions + + + polymorphic types + + + + polymorphic functions + + + + Two pseudo-types of special interest are anyelement and + anyarray, which are collectively called polymorphic + types. Any function declared using these types is said to be + a 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. + + + + 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 + anyelement is allowed to have any specific actual + data type, but in any given call they must all be the + same actual type. Each + position declared as anyarray can have any array data type, + but similarly they must all be the same type. If there are + positions declared anyarray and others declared + anyelement, the actual array type in the + anyarray positions must be an array whose elements are + the same type appearing in the anyelement positions. + + + + 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 + foo(anyelement, anyelement) will take any two input values, + so long as they are of the same data type. + + + + 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 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. + + &xfunc; + &xaggr; &xtypes; &xoper; - &xaggr; &xindex; diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index f06f289d2d..d9cb8bb018 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -7044,28 +7044,67 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); = - equals + equal ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] t + + + <> + not equal + ARRAY[1,2,3] <> ARRAY[1,2,4] + t + + + + < + less than + ARRAY[1,2,3] < ARRAY[1,2,4] + t + + + + > + greater than + ARRAY[1,4,3] > ARRAY[1,2,4] + t + + + + <= + less than or equal + ARRAY[1,2,3] <= ARRAY[1,2,3] + t + + + + >= + greater than or equal + ARRAY[1,4,3] >= ARRAY[1,4,3] + t + + || array-to-array concatenation ARRAY[1,2,3] || ARRAY[4,5,6] {{1,2,3},{4,5,6}} + || array-to-array concatenation ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]] {{1,2,3},{4,5,6},{7,8,9}} + || element-to-array concatenation 3 || ARRAY[4,5,6] {3,4,5,6} + || array-to-element concatenation diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 2f51d50cf3..b313860ea0 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -177,16 +177,54 @@ END; - - Developing in <application>PL/pgSQL</application> + + Supported Argument and Result Datatypes + + + Functions written in PL/pgSQL 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 PL/pgSQL + function as returning record, which means that the result + is a row type whose columns are determined by specification in the + calling query, as discussed in . + + + + PL/pgSQL functions may also be declared to accept + and return the polymorphic types + anyelement and anyarray. The actual + datatypes handled by a polymorphic function can vary from call to + call, as discussed in . + An example is shown in . + + + + PL/pgSQL functions can also be declared to return + a set, or table, of any datatype they can return a single + instance of. Such a function generates its output by executing + RETURN NEXT for each desired element of the result set. + + + + Finally, a PL/pgSQL function may be declared to return + void if it has no useful return value. + + + + + + Tips for Developing in <application>PL/pgSQL</application> One good way to develop in - PL/pgSQL is to simply use the text editor of your + PL/pgSQL is to use the text editor of your choice to create your functions, and in another window, use - psql to load those functions. If you are doing it this way, it + psql to load and test those functions. + If you are doing it this way, it is a good idea to write the function using 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: CREATE OR REPLACE FUNCTION testfunc(integer) RETURNS integer AS ' @@ -197,8 +235,8 @@ end; - While running psql, you can load or reload such a - function definition file with + While running psql, you can load or reload such + a function definition file with \i filename.sql @@ -213,39 +251,40 @@ end; provide convenient features such as escaping single quotes and making it easier to recreate and debug functions. - - - - Handling of Quotations Marks + + Handling of Quote Marks - Since the code of any procedural language function is specified + Since the code of a PL/pgSQL function is specified in CREATE FUNCTION 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 . 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. - 1 quotation mark + 1 quote mark - To begin/end function bodies, for example: + To begin and end the function body, for example: CREATE FUNCTION foo() RETURNS integer AS '...' LANGUAGE plpgsql; + Anywhere within the function body, quote marks must + appear in pairs. - 2 quotation marks + 2 quote marks 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''; - The second line is interpreted as + The second line is seen by PL/pgSQL as SELECT * FROM users WHERE f_name='foobar'; @@ -262,22 +301,22 @@ SELECT * FROM users WHERE f_name='foobar'; - 4 quotation marks + 4 quote marks - 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: a_output := a_output || '' AND name LIKE ''''foobar'''' AND xyz'' - The value of a_output would then be: - AND name LIKE 'foobar' AND xyz. + The value actually appended to a_output would be: + AND name LIKE 'foobar' AND xyz. - 6 quotation marks + 6 quote marks 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'' a_output := a_output || '' AND name LIKE ''''foobar'''''' - The value of a_output would then be: + The value appended to a_output would then be: AND name LIKE 'foobar'. - 10 quotation marks + 10 quote marks When you want two single quotes in a string constant (which @@ -315,6 +354,15 @@ if v_... like ''...'' then return ''...''; end if; + + + 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 \'\' instead + of ''''. Some find this easier to keep track of, some + do not. + + @@ -474,7 +522,8 @@ user_id CONSTANT integer := 10; Parameters passed to functions are named with the identifiers $1, $2, - etc. Optionally, aliases can be declared for $n + etc. Optionally, aliases can be declared for + $n 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; + + + When the return type of a PL/pgSQL + function is declared as a polymorphic type (anyelement + or anyarray), a special parameter $0 + is created. Its datatype is the actual return type of the function, + as deduced from the actual input types (see ). + This allows the function to access its actual return type + as shown in . + $0 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. $0 can also be + given an alias. For example, this function works on any datatype + that has a + operator: + +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; + + @@ -536,6 +615,15 @@ user_id users.user_id%TYPE; from integer to real), you may not need to change your function definition. + + + %TYPE 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 %TYPE to the function's + arguments or result placeholders. + + @@ -620,6 +708,14 @@ END; Note that RECORD is not a true data type, only a placeholder. + One should also realize that when a PL/pgSQL + function is declared to return 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 record the actual structure is determined when the + calling query is parsed, whereas a record variable can change its row + structure on-the-fly. @@ -965,7 +1061,7 @@ EXECUTE command-string; When working with dynamic commands you will have to face escaping of single quotes in PL/pgSQL. Please refer to the - overview in , + overview in , which can save you some effort. @@ -1004,11 +1100,11 @@ EXECUTE ''UPDATE tbl SET '' quote_literal(text). Variables containing column and table identifiers should be passed to function quote_ident. - 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 quote_literal. 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. @@ -2253,7 +2349,7 @@ CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp In PostgreSQL you need to escape single - quotes in the function body. See . + quotes in the function body. See . diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 8d23a3e354..c77456a0e8 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ @@ -867,7 +867,8 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; - 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. @@ -903,6 +904,12 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; + An array constructor. + + + + + Another value expression in parentheses, useful to group subexpressions and override precedence. @@ -1216,8 +1223,86 @@ SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) + + Array Constructors + + + arrays + constructors + + + + An array constructor is an expression that builds an + array value from values for its member elements. A simple array + constructor + consists of the keyword ARRAY, a left square bracket + [, one or more expressions (separated by commas) for the + array element values, and finally a right square bracket ]. + For example, + +SELECT ARRAY[1,2,3+4]; + array +--------- + {1,2,7} +(1 row) + + The array element type is the common type of the member expressions, + determined using the same rules as for UNION or + CASE constructs (see ). + + + + Multidimensional array values can be built by nesting array + constructors. + In the inner constructors, the keyword ARRAY may + be omitted. For example, these produce the same result: + + +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) + + + Since multidimensional arrays must be rectangular, inner constructors + at the same level must produce sub-arrays of identical dimensions. + + + + 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 ARRAY followed by a parenthesized (not + bracketed) subquery. For example: + +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) + + 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. + + + + The subscripts of an array value built with ARRAY + always begin with one. For more information about arrays, see + . + + + + - Expression Evaluation + Expression Evaluation Rules 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 101067e175..7b82af322e 100644 --- a/doc/src/sgml/xaggr.sgml +++ b/doc/src/sgml/xaggr.sgml @@ -1,5 +1,5 @@ @@ -72,8 +72,9 @@ SELECT complex_sum(a) FROM test_complex; omitting the initcond phrase, so that the initial state condition is null. Ordinarily this would mean that the sfunc would need to check for a null state-condition input, but for - sum and some other simple aggregates like max and min, - it would be sufficient to insert the first nonnull input value into + sum and some other simple aggregates like + max and 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. PostgreSQL will do that automatically if the initial condition is null and @@ -111,8 +112,55 @@ CREATE AGGREGATE avg ( - For further details see the description of the CREATE - AGGREGATE command in . + Aggregate functions may use polymorphic + state transition functions or final functions, so that the same functions + can be used to implement multiple aggregates. + See + 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: + + +CREATE AGGREGATE array_accum ( + sfunc = array_append, + basetype = anyelement, + stype = anyarray, + initcond = '{}' +); + + + Here, the actual state type for any aggregate call is the array type + having the actual input type as elements. + + + + Here's the output using two different actual data types as arguments: + + +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) + + + + + For further details see the + + command. diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 71c9ec1ce7..25997718de 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ @@ -41,22 +41,29 @@ $Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.70 2003/07/25 20:17:49 tgl E 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. + 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. + + + It's easiest to define SQL - functions, so we'll start with those. Examples in this section - can also be found in funcs.sql - and funcs.c in the tutorial directory. + functions, so we'll start by discussing those. Throughout this chapter, it can be useful to look at the reference page of the CREATE FUNCTION command to understand the examples better. + Some examples from this chapter + can be found in funcs.sql + and funcs.c in the tutorial directory. @@ -67,8 +74,7 @@ $Header: /cvsroot/pgsql/doc/src/sgml/xfunc.sgml,v 1.70 2003/07/25 20:17:49 tgl E SQL functions execute an arbitrary list of SQL statements, returning - the result of the last query in the list, which must be a - 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 the first row of a multirow @@ -276,7 +282,7 @@ CREATE FUNCTION new_emp() RETURNS emp AS ' ' LANGUAGE SQL; - 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. @@ -316,7 +322,7 @@ ERROR: function declared to return emp returns varchar instead of text at colum - 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: SELECT (new_emp()).name; @@ -330,7 +336,7 @@ SELECT (new_emp()).name; SELECT new_emp().name; -ERROR: syntax error at or near "." +ERROR: syntax error at or near "." at character 17 @@ -509,6 +515,68 @@ SELECT name, listchildren(name) FROM nodes; for those arguments, so no result rows are generated. + + + Polymorphic <acronym>SQL</acronym> Functions + + + SQL functions may be declared to accept and + return the polymorphic types + anyelement and anyarray. + See for a more detailed explanation + of polymorphic functions. Here is a polymorphic function + make_array that builds up an array from two + arbitrary data type elements: + +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) + + + + + Notice the use of the typecast 'a'::text + to specify that the argument is of type text. This is + required if the argument is just a string literal, since otherwise + it would be treated as type + unknown, and array of unknown is not a valid + type. + Without the typecast, you will get errors like this: + + +ERROR: could not determine ANYARRAY/ANYELEMENT type because input is UNKNOWN + + + + + + It is permitted to have polymorphic arguments with a deterministic + return type, but the converse is not. For example: + +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. + + + @@ -1999,6 +2067,89 @@ CREATE OR REPLACE FUNCTION testpassbyval(integer, integer) RETURNS SETOF __testp distribution contains more examples of set-returning functions. + + + Polymorphic Arguments and Return Types + + + C-language functions may be declared to accept and + return the polymorphic types + anyelement and anyarray. + See 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 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 get_fn_expr_rettype(FmgrInfo *flinfo) and + 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 flinfo is normally accessed as + fcinfo->flinfo. The parameter argnum + is zero based. + + + + 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: + + +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); +} + + + + + The following command declares the function + make_array in SQL: + + +CREATE FUNCTION make_array(anyelement) + RETURNS anyarray + AS 'DIRECTORY/funcs', 'make_array' + LANGUAGE 'C' STRICT; + + + Note the use of STRICT; this is essential since the code is not + bothering to test for a NULL input. + + -- 2.11.0