Built-in Function Call

Synopsis

Function call expression applies the specified function to to given arguments between parentheses and return the result of the computation.

Syntax

function_call ::= function_name '(' [ arguments ... ] ')'

Builtin Functions

Aggregate Functions

Semantics

• The argument data types must be convertible to the expected type for that argument that was specified by the function definition.
• Function execution will return a value of the specified type by the function definition.
• YugabyteDB allows function calls to be used any where that expression is allowed.

Cast function

cast_call ::= CAST '(' column AS type ')'

CAST function converts the value returned from a table column to the specified data type.

partition_hash function

partition_hash is a function that takes as arguments the partition key columns of the primary key of a row andreturns a uint16 hash value representing the hash value for the row used for partitioning the table.The hash values used for partitioning fall in the 0-65535 (uint16) range.Tables are partitioned into tablets, with each tablet being responsible for a range of partition values.The partition_hash of the row is used to decide which tablet the row will reside in.

partition_hash can be handy for querying a subset of the data to get approximate row counts or to breakdownfull-table operations into smaller sub-tasks that can be run in parallel.

Querying a subset of the data

One use of partition_hash is to query a subset of the data and get approximate count of rows in the table.For example, suppose we have a table t with partitioning columns (h1,h2):

create table t (h1 int, h2 int, r1 int, r2 int, v int, 
                         primary key ((h1, h2), r1, r2));

We can use this function to query a subset of the data (in this case, ¹⁄₁₂₈ of the data):

select count(*) from t where partition_hash(h1, h2) >= 0 and
                                      partition_hash(h1, h2) < 512;

The value 512 comes from dividing the full hash partition range by the number of subsets that we want to query (65536/128=512).

Parallel full table scans

To do a distributed scan, we can issue, in this case, 128 queries each using a different hash range:

.. where partition_hash(h1, h2) >= 0 and partition_hash(h1, h2) < 512;

.. where partition_hash(h1, h2) >= 512 and partition_hash(h1, h2) <1024 ;

and so on, till the last segment/range of 512 in the partition space:

.. where partition_hash(h1, h2) >= 65024;

WriteTime function

The WriteTime function returns the timestamp in microseconds when a column was written.For example, suppose we have a table page_views with a column named views:

 SELECT writetime(views) FROM page_views;
 writetime(views)
------------------
 1572882871160113
(1 rows)

TTL function

The TTL function returns the number of seconds until a column or row expires.Assuming we have a table page_views and a column named views:

SELECT TTL(views) FROM page_views;
 ttl(views)
------------
      86367
(1 rows)

Examples

cqlsh:example> CREATE TABLE test_cast (k INT PRIMARY KEY, ts TIMESTAMP);

cqlsh:example> INSERT INTO test_cast (k, ts) VALUES (1, '2018-10-09 12:00:00');

cqlsh:example> SELECT CAST(ts AS DATE) FROM test_cast;

 cast(ts as date)

   2018-10-09

See also

• All Expressions