Functions and Operators

 Many functions and operators are available in POSTGRESQL. Function calls can take zero, one, or more arguments and return a single value. You can list all functions and their arguments using psql's \df command. You can use psql's \dd command to display comments about any specific function or group of functions, as shown in Figure .   

        
        test=> \df
                                      List of functions
          Result   |      Function       |                Arguments                 
        -----------+---------------------+------------------------------------------
         _bpchar   | _bpchar             | _bpchar int4 
         _varchar  | _varchar            | _varchar int4 
         float4    | abs                 | float4 
         float8    | abs                 | float8 
        ... 
         
        test=> \df int
                          List of functions
          Result  |     Function     |       Arguments        
        ----------+------------------+------------------------
         int2     | int2             | float4 
         int2     | int2             | float8 
         int2     | int2             | int2 
         int2     | int2             | int4 
        ... 
         
        test=> \df upper
               List of functions
         Result | Function | Arguments 
        --------+----------+-----------
         text   | upper    | text 
        (1 row) 
         
        test=> \dd upper
              Object descriptions
         Name  |  Object  | Description 
        -------+----------+-------------
         upper | function | uppercase
        (1 row) 
         
        test=> SELECT upper('jacket');
         upper  
        --------
         JACKET
        (1 row) 
         
        test=> SELECT sqrt(2.0);  -- square root
              sqrt       
        -----------------
         1.4142135623731
        (1 row)
         

 Operators differ from functions in the following ways:

• Operators are symbols, not names.
• Operators usually take two arguments.
• Arguments appear to the left and right of the operator symbol.

For example, + is an operator that takes one argument on the left and one on the right, and returns the sum of the arguments. Psql's \do command lists all POSTGRESQL operators and their arguments. Figure  shows a listing of operators and examples of their use.

 

        test=> \do
                                                List of operators
         Op  |  Left arg   |  Right arg  |  Result   |      Description
        -----+-------------+-------------+-----------+----------------------------------
         !   | int2        |             | int4      | 
         !   | int4        |             | int4      | factorial
         !   | int8        |             | int8      | factorial
         !!  |             | int2        | int4      | 
        ... 
         
        test=> \do /
                                  List of operators
         Op | Left arg | Right arg |  Result  |         Description          
        ----+----------+-----------+----------+------------------------------
         /  | box      | point     | box      | divide box by point (scale)
         /  | char     | char      | char     | divide
         /  | circle   | point     | circle   | divide
         /  | float4   | float4    | float4   | divide
        ... 
         
        test=> \do ^
                             List of operators
         Op | Left arg | Right arg | Result |     Description      
        ----+----------+-----------+--------+----------------------
         ^  | float8   | float8    | float8 | exponentiation (x^y)
        (1 row) 
         
        test=> \dd ^
                  Object descriptions
         Name |  Object  |     Description      
        ------+----------+----------------------
         ^    | operator | exponentiation (x^y)
        (1 row) 
         
        test=> SELECT 2 + 3 ^ 4;
         ?column? 
        ----------
               83
        (1 row)  

  The standard arithmetic operators--addition (+), subtraction (-), multiplication (*), division (/), modulo/remainder (%), and exponentiation (^)--honor the standard precedence rules. That is, exponentiation is performed first; multiplication, division, and modulo second; and addition and subtraction last. You can use parentheses to alter this precedence. Other operators are evaluated in a left-to-right manner, unless parentheses are present.