Built-in Function Call

Syntax

Builtin Functions

Aggregate Functions

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 (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.

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) >= 512 and partition_hash(h1, h2) <1024 ;

.. 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)
------------------
(1 rows)

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> 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

Function Call