SQL Column Operator Functions

These are functions that can be used in the "columns" argument for select statements:

• cop_append(): append a string to the output of a column
• cop_as(): rename columns
• cop_avg(): return the averge value for the given column when grouping
• cop_cast(): convert column value into another datatype
• cop_coalesce(): returns the first non-NULL value in the given columns
• cop_distinct(): add DISTINCT to the column name
• cop_length(): return the length of the given text column
• cop_lower(): return a column in lower case
• cop_max(): return the maximum value for the given column when grouping
• cop_min(): return the minimum value for the given column when grouping
• cop_over(): return a hash for the "over" operator for windowing functions
• cop_prepend(): prepend a string to the output of a column
• cop_seq(): increments the sequence and returns the next value
• cop_seq_currval(): returns the current value of the given sequence without changing the sequence
• cop_substr(): returns a substring of a text column
• cop_sum(): return the sum of all values for the given column when grouping
• cop_upper(): return a column in upper case
• cop_value(): return a constant value (SQL literal) in a column
• cop_year(): return the year of the given date column
• cop_year_day(): return the year to day value of the given date column
• cop_year_hour(): return the year to hour value of the given date column
• cop_year_month(): return the year and month value of the given date column
• cop_trunc_date(): return truncated date regarding the mask
• cop_cume_dist(): analytic/window function
• cop_dense_rank(): analytic/window function
• cop_first_value(): analytic/window function
• cop_last_value(): analytic/window function
• cop_ntile(): analytic/window function
• cop_percent_rank(): analytic/window function
• cop_rank(): analytic/window function
• cop_row_number(): analytic/window function

Column operator functions can be nested as in the following example:

Example:

*list<auto> rows = t.selectRows(("columns": cop_as(cop_lower("permission_type"), "perm"), "where": ("permission_type": "USER"), "limit": 100, "offset": 200));