example
supports routine storage in the default
schema,
you can use the following:
answer()
is always identical, so you can
declare it as deterministic, and add some other information:
fullname
concatenating two strings and the input value:
BEGIN
block. It
calculates the result of a multiplication of the input integer with 99
. The
bigint
data type is used for all variables and values. The value of integer
99
is cast to bigint
in the default value assignment for the variable x
.
CASE
statement. The simple bigint
input value is compared to a number of values.
CASE
statement, this time with two
parameters, showcasing the importance of the order of the conditions.
n
-th value in the Fibonacci series, in which each
number is the sum of the two preceding ones. The two initial values are set
to 1
as the defaults for a
and b
. The routine uses an IF
statement
condition to return 1
for all input values of 2
or less. The WHILE
block
then starts to calculate each number in the series, starting with a=1
and
b=1
and iterates until it reaches the n
-th position. In each iteration is
sets a
and b
for the preceding to values, so it can calculate the sum, and
finally return it. Note that processing the routine takes longer and longer with
higher n
values, and the result is deterministic.
top
label to name the WHILE
block, and then controls
the flow with conditional statements, ITERATE
, and LEAVE
. For the values of
a=1
and a=2
in the first two iterations of the loop the ITERATE
call moves
the flow up to top
before b
is ever increased. Then b
is increased for the
values a=3
, a=4
, a=5
, a=6
, and a=7
, resulting in b=5
. The LEAVE
call then causes the exit of the block before a is increased further to 10
and
therefore the result of the routine is 5
.
n
to the power of p
by repeated
multiplication and keeping track of the number of multiplications performed.
Note that this routine does not return the correct 0
for p=0
since the top
block is merely escaped and the value of n
is returned. The same incorrect
behavior happens for negative values of p
:
7
as a result of the increase of b
in the loop from
a=3
to a=10
:
2
and shows that labels can be repeated and label usage
within a block refers to the label of that block:
length()
and cardinality()
can be used in a routine. The two nested BEGIN
blocks also show how variable names are local within these blocks x
, but the
global r
from the top-level block can be accessed in the nested blocks:
VARCHAR
into TIMESTAMP WITH TIME ZONE
. Date strings are commonly represented by ISO 8601 standard, such as
2023-12-01
, 2023-12-01T23
. Date strings are also often represented in the
YYYYmmdd
and YYYYmmddHH
format, such as 20230101
and 2023010123
. Hive
tables can use this format to represent day and hourly partitions, for example
/day=20230101
, /hour=2023010123
.
This routine parses date strings in a best-effort fashion and can be used as a
replacement for date string manipulation functions such as date
, date_parse
,
from_iso8601_date
, and from_iso8601_timestamp
.
Note that the routine defaults the time value to 00:00:00.000
and the time
zone to the session time zone.
human_readable_seconds
that
formats a number of seconds into a string:
hrd
formats a number of days into a human readable text
that provides the approximate number of years and months:
strtrunc
truncates strings longer than 60 characters,
leaving the first 30 and the last 25 characters, and cutting out extra
characters in the middle.
CASE
expression and multiple function
calls. It can therefore define the complete logic in the RETURN
clause.
The following statement shows the same capability within the routine itself.
Note the duplicate RETURN
inside and outside the CASE
statement and the
required END CASE;
. The second RETURN
statement is required, because a
routine must end with a RETURN
statement. As a result the ELSE
clause can be
omitted.
CASE
to an IF
statement, and avoids the
duplicate RETURN
:
format_number()
function. However it is using units
that don’t work well with bytes. The following format_data_size
routine can
format large values of bytes into a human readable string.
bar()
color function, but
it’s using ANSI escape codes to output colors, and thus is only usable for
displaying results in a terminal. The following example shows a similar routine,
that only uses ASCII characters.
approx_most_frequent()
, that can calculate most frequently occurring values.
It returns a map with values as keys and number of occurrences as values. Maps
are not ordered, so when displayed, the entries can change places on subsequent
runs of the same query, and readers must still compare all frequencies to find
the one most frequent value. The following is a routine returns ordered results
as a string.