]> granicus.if.org Git - postgresql/blob - doc/src/sgml/func.sgml
Update docs for TIME WITHOUT TIME ZONE.
[postgresql] / doc / src / sgml / func.sgml
1  <chapter id="functions">
2   <title id="functions-title">Functions</title>
3
4   <abstract>
5    <para>
6     Describes the built-in functions available
7     in <productname>Postgres</productname>.
8    </para>
9   </abstract>
10
11   <para>
12    Many data types have functions available for conversion to other related types.
13    In addition, there are some type-specific functions. Some functions are also
14    available through operators and may be documented as operators only.
15   </para>
16
17   <sect1>
18    <title id="sql-funcs">SQL Functions</title>
19
20    <para>
21     <firstterm><acronym>SQL</acronym> functions</firstterm> are constructs
22     defined by the <acronym>SQL92</acronym> standard which have
23     function-like syntax but which can not be implemented as simple
24     functions. 
25    </para>
26
27    <para>
28     <table tocentry="1">
29      <title>SQL Functions</title>
30      <tgroup cols="4">
31       <thead>
32        <row>
33         <entry>Function</entry>
34         <entry>Returns</entry>
35         <entry>Description</entry>
36         <entry>Example</entry>
37        </row>
38       </thead>
39       <tbody>
40        <row>
41         <entry>COALESCE(<replaceable class="parameter">list</replaceable>)</entry>
42         <entry>non-NULL</entry>
43         <entry>return first non-NULL value in list</entry>
44         <entry>COALESCE(rle, c2 + 5, 0)</entry>
45        </row>
46        <row>
47         <entry>NULLIF(<replaceable class="parameter">input</replaceable>,<replaceable class="parameter">value</replaceable>)</entry>
48         <entry><replaceable class="parameter">input</replaceable> or NULL</entry>
49         <entry>return NULL if
50         <replaceable class="parameter">input</replaceable> =
51         <replaceable class="parameter">value</replaceable>,
52         else <replaceable class="parameter">input</replaceable>
53         </entry>
54         <entry>NULLIF(c1, 'N/A')</entry>
55        </row>
56        <row>
57         <entry>CASE WHEN <replaceable class="parameter">expr</replaceable> THEN <replaceable class="parameter">expr</replaceable> [...] ELSE <replaceable class="parameter">expr</replaceable> END</entry>
58         <entry><replaceable class="parameter">expr</replaceable></entry>
59         <entry>return expression for first true WHEN clause</entry>
60         <entry>CASE WHEN c1 = 1 THEN 'match' ELSE 'no match' END</entry>
61        </row>
62       </tbody>
63      </tgroup>
64     </table>
65    </para>
66   </sect1>
67
68   <sect1>
69    <title id="math-funcs">Mathematical Functions</title>
70
71    <para>
72     <table tocentry="1">
73      <title>Mathematical Functions</title>
74      <tgroup cols="4">
75       <thead>
76        <row>
77         <entry>Function</entry>
78         <entry>Returns</entry>
79         <entry>Description</entry>
80         <entry>Example</entry>
81        </row>
82       </thead>
83       <tbody>
84        <row>
85         <entry>abs(float8)</entry>
86         <entry>float8</entry>
87         <entry>absolute value</entry>
88         <entry>abs(-17.4)</entry>
89        </row>
90        <row>
91         <entry>degrees(float8)</entry>
92         <entry>float8</entry>
93         <entry>radians to degrees</entry>
94         <entry>degrees(0.5)</entry>
95        </row>
96        <row>
97         <entry>exp(float8)</entry>
98         <entry>float8</entry>
99         <entry>raise e to the specified exponent</entry>
100         <entry>exp(2.0)</entry>
101        </row>
102        <row>
103         <entry>ln(float8)</entry>
104         <entry>float8</entry>
105         <entry>natural logarithm</entry>
106         <entry>ln(2.0)</entry>
107        </row>
108        <row>
109         <entry>log(float8)</entry>
110         <entry>float8</entry>
111         <entry>base 10 logarithm</entry>
112         <entry>log(2.0)</entry>
113        </row>
114        <row>
115         <entry>pi()</entry>
116         <entry>float8</entry>
117         <entry>fundamental constant</entry>
118         <entry>pi()</entry>
119        </row>
120        <row>
121         <entry>pow(float8,float8)</entry>
122         <entry>float8</entry>
123         <entry>raise a number to the specified exponent</entry>
124         <entry>pow(2.0, 16.0)</entry>
125        </row>
126        <row>
127         <entry>radians(float8)</entry>
128         <entry>float8</entry>
129         <entry>degrees to radians</entry>
130         <entry>radians(45.0)</entry>
131        </row>
132        <row>
133         <entry>round(float8)</entry>
134         <entry>float8</entry>
135         <entry>round to nearest integer</entry>
136         <entry>round(42.4)</entry>
137        </row>
138        <row>
139         <entry>sqrt(float8)</entry>
140         <entry>float8</entry>
141         <entry>square root</entry>
142         <entry>sqrt(2.0)</entry>
143        </row>
144        <row>
145         <entry>cbrt(float8)</entry>
146         <entry>float8</entry>
147         <entry>cube root</entry>
148         <entry>cbrt(27.0)</entry>
149        </row>
150        <row>
151         <entry>trunc(float8)</entry>
152         <entry>float8</entry>
153         <entry>truncate (towards zero)</entry>
154         <entry>trunc(42.4)</entry>
155        </row>
156        <row>
157         <entry>float(int)</entry>
158         <entry>float8</entry>
159         <entry>convert integer to floating point</entry>
160         <entry>float(2)</entry>
161        </row>
162        <row>
163         <entry>float4(int)</entry>
164         <entry>float4</entry>
165         <entry>convert integer to floating point</entry>
166         <entry>float4(2)</entry>
167        </row>
168        <row>
169         <entry>integer(float)</entry>
170         <entry>int</entry>
171         <entry>convert floating point to integer</entry>
172         <entry>integer(2.0)</entry>
173        </row>
174       </tbody>
175      </tgroup>
176     </table>
177    </para>
178
179    <para>
180     Most of the functions listed for FLOAT8 are also available for
181     type NUMERIC.
182    </para>
183
184    <para>
185     <table tocentry="1">
186      <title>Transcendental Mathematical Functions</title>
187      <tgroup cols="4">
188       <thead>
189        <row>
190         <entry>Function</entry>
191         <entry>Returns</entry>
192         <entry>Description</entry>
193         <entry>Example</entry>
194        </row>
195       </thead>
196       <tbody>
197        <row>
198         <entry>acos(float8)</entry>
199         <entry>float8</entry>
200         <entry>arccosine</entry>
201         <entry>acos(10.0)</entry>
202        </row>
203        <row>
204         <entry>asin(float8)</entry>
205         <entry>float8</entry>
206         <entry>arcsine</entry>
207         <entry>asin(10.0)</entry>
208        </row>
209        <row>
210         <entry>atan(float8)</entry>
211         <entry>float8</entry>
212         <entry>arctangent</entry>
213         <entry>atan(10.0)</entry>
214        </row>
215        <row>
216         <entry>atan2(float8,float8)</entry>
217         <entry>float8</entry>
218         <entry>arctangent</entry>
219         <entry>atan2(10.0,20.0)</entry>
220        </row>
221        <row>
222         <entry>cos(float8)</entry>
223         <entry>float8</entry>
224         <entry>cosine</entry>
225         <entry>cos(0.4)</entry>
226        </row>
227        <row>
228         <entry>cot(float8)</entry>
229         <entry>float8</entry>
230         <entry>cotangent</entry>
231         <entry>cot(20.0)</entry>
232        </row>
233        <row>
234         <entry>sin(float8)</entry>
235         <entry>float8</entry>
236         <entry>sine</entry>
237         <entry>cos(0.4)</entry>
238        </row>
239        <row>
240         <entry>tan(float8)</entry>
241         <entry>float8</entry>
242         <entry>tangent</entry>
243         <entry>tan(0.4)</entry>
244        </row>
245       </tbody>
246      </tgroup>
247     </table>
248    </para>
249
250   </sect1>
251
252   <sect1>
253    <title>String Functions</title>
254
255    <para>
256     SQL92 defines string functions with specific syntax. Some of these
257     are implemented using other <productname>Postgres</productname> functions.
258     The supported string types for <acronym>SQL92</acronym> are
259     <type>char</type>, <type>varchar</type>, and <type>text</type>.
260    </para>
261
262    <para>
263     <table tocentry="1">
264      <title><acronym>SQL92</acronym> String Functions</title>
265      <tgroup cols="4">
266       <thead>
267        <row>
268         <entry>Function</entry>
269         <entry>Returns</entry>
270         <entry>Description</entry>
271         <entry>Example</entry>
272        </row>
273       </thead>
274       <tbody>
275        <row>
276         <entry>char_length(string)</entry>
277         <entry>int4</entry>
278         <entry>length of string</entry>
279         <entry>char_length('jose')</entry>
280        </row>
281        <row>
282         <entry>character_length(string)</entry>
283         <entry>int4</entry>
284         <entry>length of string</entry>
285         <entry>char_length('jose')</entry>
286        </row>
287        <row>
288         <entry>lower(string)</entry>
289         <entry>string</entry>
290         <entry>convert string to lower case</entry>
291         <entry>lower('TOM')</entry>
292        </row>
293        <row>
294         <entry>octet_length(string)</entry>
295         <entry>int4</entry>
296         <entry>storage length of string</entry>
297         <entry>octet_length('jose')</entry>
298        </row>
299        <row>
300         <entry>position(string in string)</entry>
301         <entry>int4</entry>
302         <entry>location of specified substring</entry>
303         <entry>position('o' in 'Tom')</entry>
304        </row>
305        <row>
306         <entry>substring(string [from int] [for int])</entry>
307         <entry>string</entry>
308         <entry>extract specified substring</entry>
309         <entry>substring('Tom' from 2 for 2)</entry>
310        </row>
311        <row>
312         <entry>trim([leading|trailing|both] [string] from string)</entry>
313         <entry>string</entry>
314         <entry>trim characters from string</entry>
315         <entry>trim(both 'x' from 'xTomx')</entry>
316        </row>
317        <row>
318         <entry>upper(text)</entry>
319         <entry>text</entry>
320         <entry>convert text to upper case</entry>
321         <entry>upper('tom')</entry>
322        </row>
323       </tbody>
324      </tgroup>
325     </table>
326    </para>
327
328    <para>
329     Many additional string functions are available for text, varchar(), and char() types.
330     Some are used internally to implement the SQL92 string functions listed above.
331    </para>
332
333    <para>
334     <table tocentry="1">
335      <title>String Functions</title>
336      <tgroup cols="4">
337       <thead>
338        <row>
339         <entry>Function</entry>
340         <entry>Returns</entry>
341         <entry>Description</entry>
342         <entry>Example</entry>
343        </row>
344       </thead>
345       <tbody>
346        <row>
347         <entry>char(text)</entry>
348         <entry>char</entry>
349         <entry>convert text to char type</entry>
350         <entry>char('text string')</entry>
351        </row>
352        <row>
353         <entry>char(varchar)</entry>
354         <entry>char</entry>
355         <entry>convert varchar to char type</entry>
356         <entry>char(varchar 'varchar string')</entry>
357        </row>
358        <row>
359         <entry>initcap(text)</entry>
360         <entry>text</entry>
361         <entry>first letter of each word to upper case</entry>
362         <entry>initcap('thomas')</entry>
363        </row>
364        <row>
365         <entry>lpad(text,int,text)</entry>
366         <entry>text</entry>
367         <entry>left pad string to specified length</entry>
368         <entry>lpad('hi',4,'??')</entry>
369        </row>
370        <row>
371         <entry>ltrim(text,text)</entry>
372         <entry>text</entry>
373         <entry>left trim characters from text</entry>
374         <entry>ltrim('xxxxtrim','x')</entry>
375        </row>
376        <row>
377         <entry>textpos(text,text)</entry>
378         <entry>text</entry>
379         <entry>locate specified substring</entry>
380         <entry>position('high','ig')</entry>
381        </row>
382        <row>
383         <entry>rpad(text,int,text)</entry>
384         <entry>text</entry>
385         <entry>right pad string to specified length</entry>
386         <entry>rpad('hi',4,'x')</entry>
387        </row>
388        <row>
389         <entry>rtrim(text,text)</entry>
390         <entry>text</entry>
391         <entry>right trim characters from text</entry>
392         <entry>rtrim('trimxxxx','x')</entry>
393        </row>
394        <row>
395         <entry>substr(text,int[,int])</entry>
396         <entry>text</entry>
397         <entry>extract specified substring</entry>
398         <entry>substr('hi there',3,5)</entry>
399        </row>
400        <row>
401         <entry>text(char)</entry>
402         <entry>text</entry>
403         <entry>convert char to text type</entry>
404         <entry>text('char string')</entry>
405        </row>
406        <row>
407         <entry>text(varchar)</entry>
408         <entry>text</entry>
409         <entry>convert varchar to text type</entry>
410         <entry>text(varchar 'varchar string')</entry>
411        </row>
412        <row>
413         <entry>translate(text,from,to)</entry>
414         <entry>text</entry>
415         <entry>convert character in string</entry>
416         <entry>translate('12345', '1', 'a')</entry>
417        </row>
418        <row>
419         <entry>varchar(char)</entry>
420         <entry>varchar</entry>
421         <entry>convert char to varchar type</entry>
422         <entry>varchar('char string')</entry>
423        </row>
424        <row>
425         <entry>varchar(text)</entry>
426         <entry>varchar</entry>
427         <entry>convert text to varchar type</entry>
428         <entry>varchar('text string')</entry>
429        </row>
430       </tbody>
431      </tgroup>
432     </table>
433    </para>
434
435    <para>
436     Most functions explicitly defined for text will work for char() and varchar() arguments.
437    </para>
438   </sect1>
439
440   <sect1>
441    <title>Date/Time Functions</title>
442
443    <para>
444     The date/time functions provide a powerful set of tools
445     for manipulating various date/time types.
446    </para>
447
448    <para>
449     <table tocentry="1">
450      <title>Date/Time Functions</title>
451      <tgroup cols="4">
452       <thead>
453        <row>
454         <entry>Function</entry>
455         <entry>Returns</entry>
456         <entry>Description</entry>
457         <entry>Example</entry>
458        </row>
459       </thead>
460       <tbody>
461        <row>
462         <entry>abstime(timestamp)</entry>
463         <entry>abstime</entry>
464         <entry>convert to abstime</entry>
465         <entry>abstime(timestamp 'now')</entry>
466        </row>
467        <row>
468         <entry>age(timestamp)</entry>
469         <entry>interval</entry>
470         <entry>preserve months and years</entry>
471         <entry>age(timestamp '1957-06-13')</entry>
472        </row>
473        <row>
474         <entry>age(timestamp,timestamp)</entry>
475         <entry>interval</entry>
476         <entry>preserve months and years</entry>
477         <entry>age('now', timestamp '1957-06-13')</entry>
478        </row>
479        <row>
480         <entry>date_part(text,timestamp)</entry>
481         <entry>float8</entry>
482         <entry>portion of date</entry>
483         <entry>date_part('dow',timestamp 'now')</entry>
484        </row>
485        <row>
486         <entry>date_part(text,interval)</entry>
487         <entry>float8</entry>
488         <entry>portion of time</entry>
489         <entry>date_part('hour',interval '4 hrs 3 mins')</entry>
490        </row>
491        <row>
492         <entry>date_trunc(text,timestamp)</entry>
493         <entry>timestamp</entry>
494         <entry>truncate date</entry>
495         <entry>date_trunc('month',abstime 'now')</entry>
496        </row>
497        <row>
498         <entry>interval(reltime)</entry>
499         <entry>interval</entry>
500         <entry>convert to interval</entry>
501         <entry>interval(reltime '4 hours')</entry>
502        </row>
503        <row>
504         <entry>isfinite(timestamp)</entry>
505         <entry>bool</entry>
506         <entry>a finite time?</entry>
507         <entry>isfinite(timestamp 'now')</entry>
508        </row>
509        <row>
510         <entry>isfinite(interval)</entry>
511         <entry>bool</entry>
512         <entry>a finite time?</entry>
513         <entry>isfinite(interval '4 hrs')</entry>
514        </row>
515        <row>
516         <entry>reltime(interval)</entry>
517         <entry>reltime</entry>
518         <entry>convert to reltime</entry>
519         <entry>reltime(interval '4 hrs')</entry>
520        </row>
521        <row>
522         <entry>timestamp(date)</entry>
523         <entry>timestamp</entry>
524         <entry>convert to timestamp</entry>
525         <entry>timestamp(date 'today')</entry>
526        </row>
527        <row>
528         <entry>timestamp(date,time)</entry>
529         <entry>timestamp</entry>
530         <entry>convert to timestamp</entry>
531         <entry>timestamp(timestamp '1998-02-24',time '23:07');</entry>
532        </row>
533        <row>
534         <entry>to_char(timestamp,text)</entry>
535         <entry>text</entry>
536         <entry>convert to string</entry>
537         <entry>to_char(timestamp '1998-02-24','DD');</entry>
538        </row>
539       </tbody>
540      </tgroup>
541     </table>
542    </para>
543
544    <para>
545     For the
546     <function>date_part</function> and <function>date_trunc</function>
547     functions, arguments can be
548     `<literal>year</literal>', `<literal>month</literal>',
549     `<literal>day</literal>', `<literal>hour</literal>',
550     `<literal>minute</literal>', and `<literal>second</literal>',
551     as well as the more specialized quantities
552     `<literal>decade</literal>', `<literal>century</literal>',
553     `<literal>millennium</literal>', `<literal>millisecond</literal>',
554     and `<literal>microsecond</literal>'. 
555     <function>date_part</function> allows `<literal>dow</literal>'
556     to return day of week, '<literal>week</literal>' to return the
557     ISO-defined week of year, and `<literal>epoch</literal>' to return
558     seconds since 1970 (for <type>timestamp</type>)
559     or '<literal>epoch</literal>' to return total elapsed seconds
560     (for <type>interval</type>).
561    </para>
562   </sect1>
563
564   
565   <sect1>
566
567    <title id="formatting-funcs">Formatting Functions</title>
568
569    <note>
570     <title>Author</title>
571     <para>
572      Written by 
573      <ulink url="mailto:zakkr@zf.jcu.cz">Karel Zak</ulink>
574      on 2000-01-24.
575     </para>
576    </note>
577    <para>
578     The <productname>Postgres</productname>
579     formatting functions provide a powerful set of tools for converting 
580     various datetypes (date/time, int, float, numeric) to formatted strings 
581     and for converting from formatted strings to specific datetypes. 
582
583     <note>
584      <para>
585       The second argument for all formatting functions is a template to
586       be used for the conversion.
587      </para>
588     </note>
589    </para>
590
591    <para>
592     <table tocentry="1">
593      <title>Formatting Functions</title>
594      <tgroup cols="4">
595       <thead>
596        <row>
597         <entry>Function</entry>
598         <entry>Returns</entry>
599         <entry>Description</entry>
600         <entry>Example</entry>
601        </row>
602       </thead>
603       <tbody>
604        <row>
605         <entry>to_char(timestamp, text)</entry>
606         <entry>text</entry>
607         <entry>convert timestamp to string</entry>
608         <entry>to_char(timestamp 'now','HH12:MI:SS')</entry>
609        </row>
610        <row>
611         <entry>to_char(int, text)</entry>
612         <entry>text</entry>
613         <entry>convert int4/int8 to string</entry>
614         <entry>to_char(125, '999')</entry>
615        </row>
616        <row>
617         <entry>to_char(float, text)</entry>
618         <entry>text</entry>
619         <entry>convert float4/float8 to string</entry>
620         <entry>to_char(125.8, '999D9')</entry>
621        </row>
622        <row>
623         <entry>to_char(numeric, text)</entry>
624         <entry>text</entry>
625         <entry>convert numeric to string</entry>
626         <entry>to_char(numeric '-125.8', '999D99S')</entry>
627        </row>
628        <row>
629         <entry>to_date(text, text)</entry>
630         <entry>date</entry>
631         <entry>convert string to date</entry>
632         <entry>to_date('05 Dec 2000', 'DD Mon YYYY')</entry>
633        </row>
634        <row>
635         <entry>to_timestamp(text, text)</entry>
636         <entry>date</entry>
637         <entry>convert string to timestamp</entry>
638         <entry>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</entry>
639        </row>
640        <row>
641         <entry>to_number(text, text)</entry>
642         <entry>numeric</entry>
643         <entry>convert string to numeric</entry>
644         <entry>to_number('12,454.8-', '99G999D9S')</entry>
645        </row>
646       </tbody>
647      </tgroup>
648     </table>
649    </para>
650
651    <para>
652     <table tocentry="1">
653      <title>Templates for date/time conversions</title>
654      <tgroup cols="2">
655       <thead>
656        <row>
657         <entry>Template</entry>
658         <entry>Description</entry>
659        </row>
660       </thead>
661       <tbody>
662        <row>
663         <entry>HH</entry>
664         <entry>hour of day (01-12)</entry>
665        </row>
666        <row>
667         <entry>HH12</entry>
668         <entry>hour of day (01-12)</entry>
669        </row>       
670        <row>
671         <entry>HH24</entry>
672         <entry>hour of day (00-23)</entry>
673        </row>       
674        <row>
675         <entry>MI</entry>
676         <entry>minute (00-59)</entry>
677        </row>   
678        <row>
679         <entry>SS</entry>
680         <entry>second (00-59)</entry>
681        </row>
682        <row>
683         <entry>SSSS</entry>
684         <entry>seconds past midnight (0-86399)</entry>
685        </row>
686        <row>
687         <entry>AM or A.M. or PM or P.M.</entry>
688         <entry>meridian indicator (upper case)</entry>
689        </row>
690        <row>
691         <entry>am or a.m. or pm or p.m.</entry>
692         <entry>meridian indicator (lower case)</entry>
693        </row>
694        <row>
695         <entry>Y,YYY</entry>
696         <entry>year (4 and more digits) with comma</entry>
697        </row>
698        <row>
699         <entry>YYYY</entry>
700         <entry>year (4 and more digits)</entry>
701        </row>
702        <row>
703         <entry>YYY</entry>
704         <entry>last 3 digits of year</entry>
705        </row>
706        <row>
707         <entry>YY</entry>
708         <entry>last 2 digits of year</entry>
709        </row>
710        <row>
711         <entry>Y</entry>
712         <entry>last digit of year</entry>
713        </row>
714        <row>
715         <entry>BC or B.C. or AD or A.D.</entry>
716         <entry>year indicator (upper case)</entry>
717        </row>
718        <row>
719         <entry>bc or b.c. or ad or a.d.</entry>
720         <entry>year indicator (lower case)</entry>
721        </row>
722        <row>
723         <entry>MONTH</entry>
724         <entry>full upper case month name (9 chars)</entry>
725        </row>
726        <row>
727         <entry>Month</entry>
728         <entry>full mixed case month name (9 chars)</entry>
729        </row>
730        <row>
731         <entry>month</entry>
732         <entry>full lower case month name (9 chars)</entry>
733        </row>
734        <row>
735         <entry>MON</entry>
736         <entry>upper case abbreviated month name (3 chars)</entry>
737        </row>
738        <row>
739         <entry>Mon</entry>
740         <entry>abbreviated mixed case month name (3 chars)</entry>
741        </row>
742        <row>
743         <entry>mon</entry>
744         <entry>abbreviated lower case month name (3 chars)</entry>
745        </row>
746        <row>
747         <entry>MM</entry>
748         <entry>month (01-12)</entry>
749        </row>
750        <row>
751         <entry>DAY</entry>
752         <entry>full upper case day name (9 chars)</entry>
753        </row>
754        <row>
755         <entry>Day</entry>
756         <entry>full mixed case day name (9 chars)</entry>
757        </row>
758        <row>
759         <entry>day</entry>
760         <entry>full lower case day name (9 chars)</entry>
761        </row>
762        <row>
763         <entry>DY</entry>
764         <entry>abbreviated upper case day name (3 chars)</entry>
765        </row>
766        <row>
767         <entry>Dy</entry>
768         <entry>abbreviated mixed case day name (3 chars)</entry>
769        </row>
770        <row>
771         <entry>dy</entry>
772         <entry>abbreviated lower case day name (3 chars)</entry>
773        </row>
774        <row>
775         <entry>DDD</entry>
776         <entry>day of year (001-366)</entry>
777        </row>
778        <row>
779         <entry>DD</entry>
780         <entry>day of month (01-31)</entry>
781        </row>
782        <row>
783         <entry>D</entry>
784         <entry>day of week (1-7; SUN=1)</entry>
785        </row>
786        <row>
787         <entry>W</entry>
788         <entry>week of month (1-5) where first week start on the first day of the month</entry>
789        </row> 
790        <row>
791         <entry>WW</entry>
792         <entry>week number of year (1-53) where first week start on the first day of the year</entry>
793        </row>
794        <row>
795         <entry>CC</entry>
796         <entry>century (2 digits)</entry>
797        </row>
798        <row>
799         <entry>J</entry>
800         <entry>Julian Day (days since January 1, 4712 BC)</entry>
801        </row>
802        <row>
803         <entry>Q</entry>
804         <entry>quarter</entry>
805        </row>
806        <row>
807         <entry>RM</entry>
808         <entry>month in Roman Numerals (I-XII; I=JAN) - upper case</entry>
809        </row>
810        <row>
811         <entry>rm</entry>
812         <entry>month in Roman Numerals (I-XII; I=JAN) - lower case</entry>
813        </row>
814        <row>
815         <entry>TZ</entry>
816         <entry>timezone string - upper case (not supported in the to_timestamp())</entry>
817        </row>
818        <row>
819         <entry>tz</entry>
820         <entry>timezone string - lower case (not supported in the to_timestamp())</entry>
821        </row>
822       </tbody>
823      </tgroup>
824     </table>
825    </para>
826
827    <para>
828     All templates allow the use of prefix and suffix modifiers. Modifiers are
829     always valid for use in templates. The prefix
830     '<literal>FX</literal>' is a global modifier only.      
831    </para>
832
833    <para>
834     <table tocentry="1">
835      <title>Suffixes for templates for date/time to_char()</title>
836      <tgroup cols="3">
837       <thead>
838        <row>
839         <entry>Suffix</entry>
840         <entry>Description</entry>
841         <entry>Example</entry>
842        </row>
843       </thead>
844       <tbody>
845        <row>
846         <entry>FM</entry>
847         <entry>fill mode prefix</entry>
848         <entry>FMMonth</entry>
849        </row>
850        <row>
851         <entry>TH</entry>
852         <entry>upper ordinal number suffix</entry>
853         <entry>DDTH</entry>
854        </row>   
855        <row>
856         <entry>th</entry>
857         <entry>lower ordinal number suffix</entry>
858         <entry>DDTH</entry>
859        </row>
860        <row>
861         <entry>FX</entry>
862         <entry>FiXed format global option (see below)</entry>
863         <entry>FX Month DD Day</entry>
864        </row>   
865        <row>
866         <entry>SP</entry>
867         <entry>spell mode (not yet implemented)</entry>
868         <entry>DDSP</entry>
869        </row>       
870       </tbody>
871      </tgroup>
872     </table>
873    </para>
874
875    <para>
876     Usage notes:
877
878     <itemizedlist>
879      <listitem>
880       <para>
881        <function>to_timestamp</function> and <function>to_date</function>
882        skip multiple blank space in converted string if the <literal>FX</literal> option 
883        is not used. <literal>FX</literal> must be specified as the first item
884        in the template; for example 
885        <literal>to_timestamp('2000    JUN','YYYY MON')</literal> is right, but
886        <literal>to_timestamp('2000    JUN','FXYYYY MON')</literal> returns error,
887        because to_timestamp() expects one blank space only.
888       </para>
889      </listitem>
890
891      <listitem>
892       <para>
893        Backslash ("<literal>\</literal>") must be specified with a double backslash
894        ("<literal>\\</literal>"); for example <literal>'\\HH\\MI\\SS'</literal>.
895       </para>
896      </listitem>
897
898      <listitem>
899       <para>
900        A double quote ('"') between quotation marks is skipped and is not parsed. 
901        If you want to write a double quote to output you must preceed
902        it with a double backslash (<literal>'\\"</literal>), for
903        example <literal>'\\"YYYY Month\\"'</literal>.
904       </para>
905      </listitem>
906
907      <listitem>
908       <para>
909        <function>to_char</function> supports text without a leading
910        double quote ('"'), but any string 
911        between a quotation marks is rapidly handled and you are
912        guaranteed that it will not be interpreted as a template
913        keyword (example: <literal>'"Hello Year: "YYYY'</literal>).
914       </para>
915      </listitem>
916     </itemizedlist>
917    </para>
918
919    <para>
920     <table tocentry="1">
921      <title>Templates for to_char(<replaceable>numeric</replaceable>)</title>
922      <tgroup cols="2">
923       <thead>
924        <row>
925         <entry>Template</entry>
926         <entry>Description</entry>
927        </row>
928       </thead>
929       <tbody>
930        <row>
931         <entry>9</entry>
932         <entry>value with the specified number of digits</entry>
933        </row>
934        <row>
935         <entry>0</entry>
936         <entry>value with leading zeros</entry>
937        </row>
938        <row>
939         <entry>. (period)</entry>
940         <entry>decimal point</entry>
941        </row>       
942        <row>
943         <entry>, (comma)</entry>
944         <entry>group (thousand) separator</entry>
945        </row>
946        <row>
947         <entry>PR</entry>
948         <entry>negative value in angle brackets</entry>
949        </row>
950        <row>
951         <entry>S</entry>
952         <entry>negative value with minus sign (use locales)</entry>
953        </row>
954        <row>
955         <entry>L</entry>
956         <entry>currency symbol (use locales)</entry>
957        </row>
958        <row>
959         <entry>D</entry>
960         <entry>decimal point (use locales)</entry>
961        </row>
962        <row>
963         <entry>G</entry>
964         <entry>group separator (use locales)</entry>
965        </row>
966        <row>
967         <entry>MI</entry>
968         <entry>minus sign on specified position (if number < 0)</entry>
969        </row>
970        <row>
971         <entry>PL</entry>
972         <entry>plus sign on specified position (if number > 0)</entry>
973        </row>
974        <row>
975         <entry>SG</entry>
976         <entry>plus/minus sign on specified position</entry>
977        </row>
978        <row>
979         <entry>RN</entry>
980         <entry>roman numeral (input between 1 and 3999)</entry>
981        </row>
982        <row>
983         <entry>TH or th</entry>
984         <entry>convert to ordinal number</entry>
985        </row>
986        <row>
987         <entry>V</entry>
988         <entry>Shift <replaceable>n</replaceable> digits (see
989          notes)</entry>
990        </row>
991        <row>
992         <entry>EEEE</entry>
993         <entry>science numbers. Now not supported.</entry>
994        </row>
995       </tbody>
996      </tgroup>
997     </table>
998    </para>
999
1000    <para>
1001     Usage notes:
1002
1003     <itemizedlist>
1004      <listitem>
1005       <para>
1006        A sign formatted using 'SG', 'PL' or 'MI' is not an anchor in
1007        the number; for example,
1008        to_char(-12, 'S9999') produces <literal>'  -12'</literal>,
1009        but to_char(-12, 'MI9999') produces <literal>'-  12'</literal>.
1010        The Oracle implementation does not allow the use of
1011        <literal>MI</literal> ahead of <literal>9</literal>, but rather
1012        requires that <literal>9</literal> preceeds
1013        <literal>MI</literal>.
1014       </para>
1015      </listitem>
1016
1017      <listitem>
1018       <para>
1019        <literal>PL</literal>, <literal>SG</literal>, and
1020        <literal>TH</literal> are <productname>Postgres</productname>
1021        extensions. 
1022       </para>
1023      </listitem>
1024
1025      <listitem>
1026       <para>
1027        <literal>9</literal> specifies a value with the same number of 
1028        digits as there are <literal>9</literal>s. If a digit is
1029        not available use blank space.
1030       </para>
1031      </listitem>
1032
1033      <listitem>
1034       <para>
1035        <literal>TH</literal> does not convert values less than zero
1036        and does not convert decimal numbers. <literal>TH</literal> is
1037        a <productname>Postgres</productname> extension.
1038       </para>
1039      </listitem>
1040
1041      <listitem>
1042       <para>
1043        <literal>V</literal> effectively
1044        multiplies the input values by
1045        <literal>10^<replaceable>n</replaceable></literal>, where
1046        <replaceable>n</replaceable> is the number of digits following
1047        <literal>V</literal>. 
1048        <function>to_char</function> does not support the use of
1049        <literal>V</literal> combined with a decimal point
1050        (e.g. "99.9V99" is not allowed).
1051       </para>
1052      </listitem>
1053     </itemizedlist>
1054    </para>   
1055
1056    <para>
1057     <table tocentry="1">
1058      <title><function>to_char</function> Examples</title>
1059      <tgroup cols="2">
1060       <thead>
1061        <row>
1062         <entry>Input</entry>
1063         <entry>Output</entry>
1064        </row>
1065       </thead>
1066       <tbody>
1067        <row>
1068         <entry>to_char(now(),'Day, HH12:MI:SS')</entry>
1069         <entry><literal>'Tuesday  , 05:39:18'</literal></entry>
1070        </row>
1071        <row>
1072         <entry>to_char(now(),'FMDay, HH12:MI:SS')</entry>
1073         <entry><literal>'Tuesday, 05:39:18'</literal></entry>
1074        </row>          
1075        <row>
1076         <entry>to_char(-0.1,'99.99')</entry>
1077         <entry><literal>' -.10'</literal></entry>
1078        </row>
1079        <row>
1080         <entry>to_char(-0.1,'FM9.99')</entry>
1081         <entry><literal>'-.1'</literal></entry>
1082        </row>
1083        <row>
1084         <entry>to_char(0.1,'0.9')</entry>
1085         <entry><literal>' 0.1'</literal></entry>
1086        </row>
1087        <row>
1088         <entry>to_char(12,'9990999.9')</entry>
1089         <entry><literal>'    0012.0'</literal></entry>
1090        </row>
1091        <row>
1092         <entry>to_char(12,'FM9990999.9')</entry>
1093         <entry><literal>'0012'</literal></entry>
1094        </row>
1095        <row>
1096         <entry>to_char(485,'999')</entry>
1097         <entry><literal>' 485'</literal></entry>
1098        </row>
1099        <row>
1100         <entry>to_char(-485,'999')</entry>
1101         <entry><literal>'-485'</literal></entry>
1102        </row>
1103        <row>
1104         <entry>to_char(485,'9 9 9')</entry>
1105         <entry><literal>' 4 8 5'</literal></entry>
1106        </row>
1107        <row>
1108         <entry>to_char(1485,'9,999')</entry>
1109         <entry><literal>' 1,485'</literal></entry>
1110        </row>
1111        <row>
1112         <entry>to_char(1485,'9G999')</entry>
1113         <entry><literal>' 1 485'</literal></entry>
1114        </row>
1115        <row>
1116         <entry>to_char(148.5,'999.999')</entry>
1117         <entry><literal>' 148.500'</literal></entry>
1118        </row>
1119        <row>
1120         <entry>to_char(148.5,'999D999')</entry>
1121         <entry><literal>' 148,500'</literal></entry>     
1122        </row>
1123        <row>
1124         <entry>to_char(3148.5,'9G999D999')</entry>
1125         <entry><literal>' 3 148,500'</literal></entry>
1126        </row>
1127        <row>
1128         <entry>to_char(-485,'999S')</entry>
1129         <entry><literal>'485-'</literal></entry>
1130        </row>
1131        <row>            
1132         <entry>to_char(-485,'999MI')</entry>
1133         <entry><literal>'485-'</literal></entry>        
1134        </row>
1135        <row>
1136         <entry>to_char(485,'999MI')</entry>
1137         <entry><literal>'485'</literal></entry>         
1138        </row>
1139        <row>
1140         <entry>to_char(485,'PL999')</entry>
1141         <entry><literal>'+485'</literal></entry>        
1142        </row>
1143        <row>            
1144         <entry>to_char(485,'SG999')</entry>
1145         <entry><literal>'+485'</literal></entry>        
1146        </row>
1147        <row>
1148         <entry>to_char(-485,'SG999')</entry>
1149         <entry><literal>'-485'</literal></entry>        
1150        </row>
1151        <row>
1152         <entry>to_char(-485,'9SG99')</entry>
1153         <entry><literal>'4-85'</literal></entry>        
1154        </row>
1155        <row>
1156         <entry>to_char(-485,'999PR')</entry>
1157         <entry><literal>'&lt;485&gt;'</literal></entry>         
1158        </row>
1159        <row>
1160         <entry>to_char(485,'L999')</entry>
1161         <entry><literal>'DM 485</literal></entry>        
1162        </row>
1163        <row>
1164         <entry>to_char(485,'RN')</entry>                
1165         <entry><literal>'        CDLXXXV'</literal></entry>
1166        </row>
1167        <row>
1168         <entry>to_char(485,'FMRN')</entry>      
1169         <entry><literal>'CDLXXXV'</literal></entry>
1170        </row>
1171        <row>
1172         <entry>to_char(5.2,'FMRN')</entry>
1173         <entry><literal>V</literal></entry>             
1174        </row>
1175        <row>
1176         <entry>to_char(482,'999th')</entry>
1177         <entry><literal>' 482nd'</literal></entry>                              
1178        </row>
1179        <row>
1180         <entry>to_char(485, '"Good number:"999')</entry>
1181         <entry><literal>'Good number: 485'</literal></entry>
1182        </row>
1183        <row>
1184         <entry>to_char(485.8,'"Pre-decimal:"999" Post-decimal:" .999')</entry>
1185         <entry><literal>'Pre-decimal: 485 Post-decimal: .800'</literal></entry>
1186        </row>
1187        <row>
1188         <entry>to_char(12,'99V999')</entry>             
1189         <entry><literal>' 12000'</literal></entry>
1190        </row>
1191        <row>
1192         <entry>to_char(12.4,'99V999')</entry>
1193         <entry><literal>' 12400'</literal></entry>
1194        </row>
1195        <row>            
1196         <entry>to_char(12.45, '99V9')</entry>
1197         <entry><literal>' 125'</literal></entry>
1198        </row>
1199       </tbody>
1200      </tgroup>
1201     </table>
1202    </para>
1203   </sect1>
1204
1205
1206   <sect1>
1207    <title>Geometric Functions</title>
1208
1209    <para>
1210     The geometric types point, box, lseg, line, path, polygon, and
1211     circle have a large set of native support functions.
1212    </para>
1213
1214    <para>
1215     <table tocentry="1">
1216      <title>Geometric Functions</title>
1217      <tgroup cols="4">
1218       <thead>
1219        <row>
1220         <entry>Function</entry>
1221         <entry>Returns</entry>
1222         <entry>Description</entry>
1223         <entry>Example</entry>
1224        </row>
1225       </thead>
1226       <tbody>
1227        <row>
1228         <entry>area(object)</entry>
1229         <entry>float8</entry>
1230         <entry>area of item</entry>
1231         <entry>area(box '((0,0),(1,1))')</entry>
1232        </row>
1233        <row>
1234         <entry>box(box,box)</entry>
1235         <entry>box</entry>
1236         <entry>intersection box</entry>
1237         <entry>box(box '((0,0),(1,1))',box '((0.5,0.5),(2,2))')</entry>
1238        </row>
1239        <row>
1240         <entry>center(object)</entry>
1241         <entry>point</entry>
1242         <entry>center of item</entry>
1243         <entry>center(box '((0,0),(1,2))')</entry>
1244        </row>
1245        <row>
1246         <entry>diameter(circle)</entry>
1247         <entry>float8</entry>
1248         <entry>diameter of circle</entry>
1249         <entry>diameter(circle '((0,0),2.0)')</entry>
1250        </row>
1251        <row>
1252         <entry>height(box)</entry>
1253         <entry>float8</entry>
1254         <entry>vertical size of box</entry>
1255         <entry>height(box '((0,0),(1,1))')</entry>
1256        </row>
1257        <row>
1258         <entry>isclosed(path)</entry>
1259         <entry>bool</entry>
1260         <entry>a closed path?</entry>
1261         <entry>isclosed(path '((0,0),(1,1),(2,0))')</entry>
1262        </row>
1263        <row>
1264         <entry>isopen(path)</entry>
1265         <entry>bool</entry>
1266         <entry>an open path?</entry>
1267         <entry>isopen(path '[(0,0),(1,1),(2,0)]')</entry>
1268        </row>
1269        <row>
1270         <entry>length(object)</entry>
1271         <entry>float8</entry>
1272         <entry>length of item</entry>
1273         <entry>length(path '((-1,0),(1,0))')</entry>
1274        </row>
1275        <row>
1276         <entry>pclose(path)</entry>
1277         <entry>path</entry>
1278         <entry>convert path to closed</entry>
1279         <entry>popen(path '[(0,0),(1,1),(2,0)]')</entry>
1280        </row>
1281 <!--
1282 Not defined by this name. Implements the intersection operator '#'
1283        <row>
1284         <entry>point(lseg,lseg)</entry>
1285         <entry>point</entry>
1286         <entry>intersection</entry>
1287         <entry>point(lseg '((-1,0),(1,0))',lseg '((-2,-2),(2,2))')</entry>
1288        </row>
1289 -->
1290        <row>
1291         <entry>npoint(path)</entry>
1292         <entry>int4</entry>
1293         <entry>number of points</entry>
1294         <entry>npoints(path '[(0,0),(1,1),(2,0)]')</entry>
1295        </row>
1296        <row>
1297         <entry>popen(path)</entry>
1298         <entry>path</entry>
1299         <entry>convert path to open path</entry>
1300         <entry>popen(path '((0,0),(1,1),(2,0))')</entry>
1301        </row>
1302        <row>
1303         <entry>radius(circle)</entry>
1304         <entry>float8</entry>
1305         <entry>radius of circle</entry>
1306         <entry>radius(circle '((0,0),2.0)')</entry>
1307        </row>
1308        <row>
1309         <entry>width(box)</entry>
1310         <entry>float8</entry>
1311         <entry>horizontal size</entry>
1312         <entry>width(box '((0,0),(1,1))')</entry>
1313        </row>
1314       </tbody>
1315      </tgroup>
1316     </table>
1317    </para>
1318
1319    <para>
1320     <table tocentry="1">
1321      <title>Geometric Type Conversion Functions</title>
1322      <tgroup cols="4">
1323       <thead>
1324        <row>
1325         <entry>Function</entry>
1326         <entry>Returns</entry>
1327         <entry>Description</entry>
1328         <entry>Example</entry>
1329        </row>
1330       </thead>
1331       <tbody>
1332        <row>
1333         <entry>box(circle)</entry>
1334         <entry>box</entry>
1335         <entry>circle to box</entry>
1336         <entry>box('((0,0),2.0)'::circle)</entry>
1337        </row>
1338        <row>
1339         <entry>box(point,point)</entry>
1340         <entry>box</entry>
1341         <entry>points to box</entry>
1342         <entry>box('(0,0)'::point,'(1,1)'::point)</entry>
1343        </row>
1344        <row>
1345         <entry>box(polygon)</entry>
1346         <entry>box</entry>
1347         <entry>polygon to box</entry>
1348         <entry>box('((0,0),(1,1),(2,0))'::polygon)</entry>
1349        </row>
1350        <row>
1351         <entry>circle(box)</entry>
1352         <entry>circle</entry>
1353         <entry>to circle</entry>
1354         <entry>circle('((0,0),(1,1))'::box)</entry>
1355        </row>
1356        <row>
1357         <entry>circle(point,float8)</entry>
1358         <entry>circle</entry>
1359         <entry>point to circle</entry>
1360         <entry>circle('(0,0)'::point,2.0)</entry>
1361        </row>
1362        <row>
1363         <entry>lseg(box)</entry>
1364         <entry>lseg</entry>
1365         <entry>box diagonal to lseg</entry>
1366         <entry>lseg('((-1,0),(1,0))'::box)</entry>
1367        </row>
1368        <row>
1369         <entry>lseg(point,point)</entry>
1370         <entry>lseg</entry>
1371         <entry>points to lseg</entry>
1372         <entry>lseg('(-1,0)'::point,'(1,0)'::point)</entry>
1373        </row>
1374        <row>
1375         <entry>path(polygon)</entry>
1376         <entry>point</entry>
1377         <entry>polygon to path</entry>
1378         <entry>path('((0,0),(1,1),(2,0))'::polygon)</entry>
1379        </row>
1380        <row>
1381         <entry>point(circle)</entry>
1382         <entry>point</entry>
1383         <entry>center</entry>
1384         <entry>point('((0,0),2.0)'::circle)</entry>
1385        </row>
1386        <row>
1387         <entry>point(lseg,lseg)</entry>
1388         <entry>point</entry>
1389         <entry>intersection</entry>
1390         <entry>point('((-1,0),(1,0))'::lseg, '((-2,-2),(2,2))'::lseg)</entry>
1391        </row>
1392        <row>
1393         <entry>point(polygon)</entry>
1394         <entry>point</entry>
1395         <entry>center</entry>
1396         <entry>point('((0,0),(1,1),(2,0))'::polygon)</entry>
1397        </row>
1398        <row>
1399         <entry>polygon(box)</entry>
1400         <entry>polygon</entry>
1401         <entry>12 point polygon</entry>
1402         <entry>polygon('((0,0),(1,1))'::box)</entry>
1403        </row>
1404        <row>
1405         <entry>polygon(circle)</entry>
1406         <entry>polygon</entry>
1407         <entry>12-point polygon</entry>
1408         <entry>polygon('((0,0),2.0)'::circle)</entry>
1409        </row>
1410        <row>
1411         <entry>polygon(<replaceable class="parameter">npts</replaceable>,circle)</entry>
1412         <entry>polygon</entry>
1413         <entry><replaceable class="parameter">npts</replaceable> polygon</entry>
1414         <entry>polygon(12,'((0,0),2.0)'::circle)</entry>
1415        </row>
1416        <row>
1417         <entry>polygon(path)</entry>
1418         <entry>polygon</entry>
1419         <entry>path to polygon</entry>
1420         <entry>polygon('((0,0),(1,1),(2,0))'::path)</entry>
1421        </row>
1422       </tbody>
1423      </tgroup>
1424     </table>
1425    </para>
1426
1427    <para>
1428     <table tocentry="1">
1429      <title>Geometric Upgrade Functions</title>
1430      <tgroup cols="4">
1431       <thead>
1432        <row>
1433         <entry>Function</entry>
1434         <entry>Returns</entry>
1435         <entry>Description</entry>
1436         <entry>Example</entry>
1437        </row>
1438       </thead>
1439       <tbody>
1440        <row>
1441         <entry>isoldpath(path)</entry>
1442         <entry>path</entry>
1443         <entry>test path for pre-v6.1 form</entry>
1444         <entry>isoldpath('(1,3,0,0,1,1,2,0)'::path)</entry>
1445        </row>
1446        <row>
1447         <entry>revertpoly(polygon)</entry>
1448         <entry>polygon</entry>
1449         <entry>to pre-v6.1</entry>
1450         <entry>revertpoly('((0,0),(1,1),(2,0))'::polygon)</entry>
1451        </row>
1452        <row>
1453         <entry>upgradepath(path)</entry>
1454         <entry>path</entry>
1455         <entry>to pre-v6.1</entry>
1456         <entry>upgradepath('(1,3,0,0,1,1,2,0)'::path)</entry>
1457        </row>
1458        <row>
1459         <entry>upgradepoly(polygon)</entry>
1460         <entry>polygon</entry>
1461         <entry>to pre-v6.1</entry>
1462         <entry>upgradepoly('(0,1,2,0,1,0)'::polygon)</entry>
1463        </row>
1464       </tbody>
1465      </tgroup>
1466     </table>
1467    </para>
1468   </sect1>
1469
1470   <sect1>
1471    <title id="cidr-funcs">IP V4 Functions</title>
1472
1473    <para>
1474     <table tocentry="1">
1475      <title><productname>Postgres</productname>IP V4 Functions</title>
1476      <tgroup cols="4">
1477       <thead>
1478        <row>
1479         <entry>Function</entry>
1480         <entry>Returns</entry>
1481         <entry>Description</entry>
1482         <entry>Example</entry>
1483        </row>
1484       </thead>
1485       <tbody>
1486        <row>
1487         <entry>broadcast(cidr)</entry>
1488         <entry>text</entry>
1489         <entry>construct broadcast address as text</entry>
1490         <entry>broadcast('192.168.1.5/24')</entry>
1491        </row>
1492        <row>
1493         <entry>broadcast(inet)</entry>
1494         <entry>text</entry>
1495         <entry>construct broadcast address as text</entry>
1496         <entry>broadcast('192.168.1.5/24')</entry>
1497        </row>
1498        <row>
1499         <entry>host(inet)</entry>
1500         <entry>text</entry>
1501         <entry>extract host address as text</entry>
1502         <entry>host('192.168.1.5/24')</entry>
1503        </row>
1504        <row>
1505         <entry>masklen(cidr)</entry>
1506         <entry>int4</entry>
1507         <entry>calculate netmask length</entry>
1508         <entry>masklen('192.168.1.5/24')</entry>
1509        </row>
1510        <row>
1511         <entry>masklen(inet)</entry>
1512         <entry>int4</entry>
1513         <entry>calculate netmask length</entry>
1514         <entry>masklen('192.168.1.5/24')</entry>
1515        </row>
1516        <row>
1517         <entry>netmask(inet)</entry>
1518         <entry>text</entry>
1519         <entry>construct netmask as text</entry>
1520         <entry>netmask('192.168.1.5/24')</entry>
1521        </row>
1522       </tbody>
1523      </tgroup>
1524     </table>
1525    </para>
1526
1527   </sect1>
1528
1529   <sect1>
1530    <title id="aggregate-funcs">Aggregate Functions</title>
1531
1532    <note>
1533     <title>Author</title>
1534     <para>
1535      Written by <ulink url="mailto:isaac@azartmedia.com">Isaac Wilcox</ulink>
1536      on 2000-06-16.
1537     </para>
1538    </note>
1539
1540    <para>
1541     <firstterm>Aggregate functions</firstterm> allow the generation of simple
1542     statistics about the values of particular columns over the selected set
1543     of rows.
1544 <!--
1545         See also <xref linkend="sql" endterm="aggregates-tutorial"> and
1546     <xref linkend="syntax" endterm="aggregates-syntax">.
1547 -->
1548         See also <xref linkend="syntax" endterm="aggregates-syntax">;
1549         refer to
1550         the <citetitle>PostgreSQL Tutorial</citetitle> for additional
1551         introductory information.
1552    </para>
1553
1554    <para>
1555     <table tocentry="1">
1556      <title>Aggregate Functions</title>
1557      <tgroup cols="4">
1558       <thead>
1559        <row>
1560         <entry>Function</entry>
1561         <entry>Returns</entry>
1562         <entry>Description</entry>
1563         <entry>Example</entry>
1564         <entry>Notes</entry>
1565        </row>
1566       </thead>
1567       <tbody>
1568        <row>
1569         <entry>COUNT(*)</entry>
1570         <entry>int4</entry>
1571         <entry>Counts the selected rows.</entry>
1572         <entry>COUNT(*)</entry>
1573         <entry></entry>
1574        </row>
1575        <row>
1576         <entry>COUNT(<replaceable class="parameter">column-name</replaceable>)</entry>
1577         <entry>int4</entry>
1578         <entry>Counts the selected rows for which the value of <replaceable class="parameter">column-name</replaceable> is not NULL.</entry>
1579         <entry>COUNT(age)</entry>
1580         <entry></entry>
1581        </row>
1582        <row>
1583         <entry>SUM(<replaceable class="parameter">column-name</replaceable>)</entry>
1584         <entry>Same as the data type of the column being summed.</entry>
1585         <entry>Finds the total obtained by adding the values of <replaceable class="parameter">column-name</replaceable> across all selected rows.</entry>
1586         <entry>SUM(hours)</entry>
1587         <entry>Summation is supported on the following data types: int8, int4, int2, float4, float8, money, interval, numeric</entry>
1588        </row>
1589        <row>
1590         <entry>MAX(<replaceable class="parameter">column-name</replaceable>)</entry>
1591         <entry>Same as the data type of the column whose maximum value is sought.</entry>
1592         <entry>The maximum value of <replaceable class="parameter">column-name</replaceable> across all selected rows.</entry>
1593         <entry>MAX(age)</entry>
1594         <entry>Finding the maximum value is supported on the following data types: int8, int4, int2, float4, float8, date, time, timetz, money, timestamp, interval, text, numeric.</entry>
1595        </row>
1596        <row>
1597         <entry>MIN(<replaceable class="parameter">column-name</replaceable>)</entry>
1598         <entry>same as the data type of the column whose minimum value is sought.</entry>
1599         <entry>The minimum value of <replaceable class="parameter">column-name</replaceable> across all selected rows.</entry>
1600         <entry>MIN(age)</entry>
1601         <entry>Finding the minimum value is supported on the following data types: int8, int4, int2, float4, float8, date, time, timetz, money, timestamp, interval, text, numeric.</entry>
1602        </row>
1603        <row>
1604         <entry>AVG(<replaceable class="parameter">column-name</replaceable>)</entry>
1605         <entry>Same as the data type of the column being averaged.</entry>
1606         <entry>The average (mean) of the values in the given column across all selected rows.</entry>
1607         <entry>AVG(age)</entry>
1608         <entry>Finding the mean value is supported on the following data types: int8, int4, int2, float4, float8, money, interval, numeric. Note that as the return type is the same as that of the data being averaged, using AVG() on discrete data will give a rounded result.</entry>
1609        </row>
1610       </tbody>
1611      </tgroup>
1612     </table>
1613    </para>
1614   </sect1>
1615  </chapter>
1616
1617 <!-- Keep this comment at the end of the file
1618 Local variables:
1619 mode:sgml
1620 sgml-omittag:nil
1621 sgml-shorttag:t
1622 sgml-minimize-attributes:nil
1623 sgml-always-quote-attributes:t
1624 sgml-indent-step:1
1625 sgml-indent-data:t
1626 sgml-parent-document:nil
1627 sgml-default-dtd-file:"./reference.ced"
1628 sgml-exposed-tags:nil
1629 sgml-local-catalogs:("/usr/lib/sgml/catalog")
1630 sgml-local-ecat-files:nil
1631 End:
1632 -->