github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-user-defined-function.md (about)

     1  ---
     2  layout: default
     3  title: User Defined Function - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # User Defined Function
     8  
     9  A User Defined Function is a routine that can be called just like built-in functions.
    10  A function has some input parameters, and [returns](#return) a single value.
    11  
    12  Functions create local scopes.
    13  [Variables]({{ '/reference/variable.html' | relative_url }}), [cursors]({{ '/reference/cursor.html' | relative_url }}), [temporary tables]({{ '/reference/temporary-table.html' | relative_url }}), and [functions]({{ '/reference/user-defined-function.html' | relative_url }}) declared in user defined functions can be refered only within the functions. 
    14  
    15  * [Scalar Function](#scalar)
    16  * [Aggregate Function](#aggregate)
    17  * [DISPOSE FUNCTION Statement](#dispose)
    18  * [RETURN Statement](#return)
    19  
    20  ## Scalar Function
    21  {: #scalar}
    22  
    23  ### Declaration
    24  {: #scalar_declaration}
    25  
    26  ```sql
    27  scalar_function_declaration
    28    : DECLARE function_name FUNCTION ([parameter [, parameter ...] [, optional_parameter ...]])
    29      AS
    30      BEGIN
    31        statements
    32      END;
    33  
    34  optional_parameter
    35    : parameter DEFAULT value
    36  ```
    37  
    38  _function_name_
    39  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    40  
    41  _statements_
    42  : [Statements]({{ '/reference/statement.html' | relative_url }})
    43  
    44  _parameter_
    45  : [Variable]({{ '/reference/variable.html' | relative_url }})
    46  
    47  _value_
    48  : [value]({{ '/reference/statement.html' | relative_url }})
    49  
    50  A scalar function takes some arguments, and returns a value.
    51  In the statements, arguments are set to variables specified in the declaration as _parameters_.
    52  
    53  
    54  #### Usage
    55  
    56  ```sql
    57  function_name([argument, [, argument ...]])
    58  ```
    59  
    60  _function_name_
    61  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    62  
    63  _argument_
    64  : [value]({{ '/reference/value.html' | relative_url }})
    65  
    66  
    67  ## Aggregate Function
    68  {: #aggregate}
    69  
    70  ### Declaration
    71  {: #aggregate_declaration}
    72  
    73  ```sql
    74  aggregate_function_declaration
    75    : DECLARE function_name AGGREGATE (cursor_name [, parameter ...] [, optional_parameter ...])
    76      AS
    77      BEGIN
    78        statements
    79      END;
    80  
    81  optional_parameter
    82    : parameter DEFAULT value
    83  ```
    84  
    85  _function_name_
    86  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    87  
    88  _cursor_name_
    89  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
    90  
    91  _statements_
    92  : [Statements]({{ '/reference/statement.html' | relative_url }})
    93  
    94  _parameter_
    95  : [Variable]({{ '/reference/variable.html' | relative_url }})
    96    
    97  _value_
    98  : [value]({{ '/reference/statement.html' | relative_url }})
    99  
   100  An aggregate function takes at least one argument, and returns a value.
   101  The first argument is a representation of grouped values, and the following arguments are parameters.
   102  
   103  In the statements, grouped values represented by the first argument can be referred with a pseudo cursor named as _cursor_name_, 
   104  and the second argument and the followings are set to variables specified in the declaration as _parameters_.
   105  You can use the [Fetch Statement]({{ '/reference/cursor.html#fetch' | relative_url }}), [While In Statement]({{ '/reference/control-flow.html#while_in_loop' | relative_url }}) or the [Cursor Status Expressions]({{ '/reference/cursor.html#status' | relative_url }}) against the pseudo cursor. 
   106  
   107  
   108  #### Usage
   109  
   110  You can use a user defined aggregate function as an [Aggregate Function]({{ '/reference/aggregate-functions.html' | relative_url }}) or an [Analytic Function]({{ '/reference/analytic-functions.html' | relative_url }}).
   111  
   112  ##### As an Aggregate Function
   113  
   114  ```sql
   115  function_name([DISTINCT] expr [, argument ...])
   116  ```
   117  
   118  _function_name_
   119  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
   120  
   121  _expr_
   122  : [value]({{ '/reference/value.html' | relative_url }})
   123  
   124  _argument_
   125  : [value]({{ '/reference/value.html' | relative_url }})
   126  
   127  ##### As an Analytic Function
   128  
   129  ```sql
   130  function_name([DISTINCT] expr [, argument ...]) OVER ([partition_clause] [order_by_clause [windowing_clause]])
   131  ```
   132  
   133  _function_name_
   134  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
   135  
   136  _expr_
   137  : [value]({{ '/reference/value.html' | relative_url }})
   138  
   139  _argument_
   140  : [value]({{ '/reference/value.html' | relative_url }})
   141  
   142  _partition_clause_
   143  : [Partition Clause]({{ '/reference/analytic-functions.html#syntax' | relative_url }})
   144  
   145  _order_by_clause_
   146  : [Order By Clause]({{ '/reference/select-query.html#order_by_clause' | relative_url }})
   147  
   148  _windowing_clause_
   149  : [Windowing Clause]({{ '/reference/analytic-functions.html#syntax' | relative_url }})
   150  
   151  
   152  Example:
   153  
   154  ```sql
   155  DECLARE product AGGREGATE (list, @default DEFAULT 0)
   156  AS
   157  BEGIN
   158      VAR @value, @fetch;
   159  
   160      WHILE @fetch IN list
   161      DO
   162          VAR @floatVal := FLOAT(@fetch);
   163          
   164          IF @floatVal IS NULL THEN
   165              CONTINUE;
   166          END IF;
   167  
   168          IF @value IS NULL THEN
   169              @value := @floatVal;
   170              CONTINUE;
   171          END IF;
   172  
   173          @value := @value * @floatVal;
   174      END WHILE;
   175      
   176      IF @value IS NULL THEN
   177          @value := @default;
   178      END IF;
   179  
   180      RETURN @value;
   181  END;
   182  
   183  SELECT product(i) FROM numbers;
   184  
   185  SELECT product(i, NULL) FROM numbers;
   186  
   187  SELECT i, product(i) OVER (order by i) FROM numbers;
   188  ```
   189  
   190  ## DISPOSE FUNCTION Statement
   191  {: #dispose}
   192  
   193  A DISPOSE FUNCTION statement disposes user defined function named as _function_name_.
   194  
   195  ```sql
   196  DISPOSE FUNCTION function_name; 
   197  ```
   198  
   199  _function_name_
   200  : [identifier]({{ '/reference/statement.html#parsing' | relative_url }})
   201  
   202  
   203  ## RETURN Statement
   204  {: #return}
   205  
   206  A RETURN statement terminates executing function, then returns a value.
   207  If the return value is not specified, then returns a null.
   208  
   209  When there is no return statement, the function executes all the statements and returns a null.
   210  
   211  ```sql
   212  RETURN [value];
   213  ```
   214  
   215  _value_
   216  : [value]({{ '/reference/value.html' | relative_url }})