array_ndims
The number of an array's dimensions. | array_ndims($a::anyarray)
=>
CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER)
| | | |
array_dims
The text of an array's dimensions, such as [1:2][1:3]. | array_dims($a::anyarray)
=>
array_to_string(list_transform(range(CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER)), _d -> '[1:' || json_array_length(to_json($a), '$' || repeat('[0]', CAST(_d AS INTEGER))) || ']'), '')
| | | |
array_lower
The lower bound of a dimension: 1, as a list's index starts there. | array_lower($a::anyarray, $d::integer)
=>
CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER) THEN 1 END AS INTEGER)
| | | |
array_upper
The upper bound of a dimension: its length. | array_upper($a::anyarray, $d::integer)
=>
CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER) THEN json_array_length(to_json($a), '$' || repeat('[0]', CAST($d - 1 AS INTEGER))) END AS INTEGER)
| | | |
array_length
The length of a dimension; NULL for a dimension the array does not have. | array_length($a::anyarray, 1)
=>
CAST(CASE WHEN len($a) > 0 THEN len($a) END AS INTEGER)
array_length($a::anyarray, $d::integer)
=>
CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER) THEN json_array_length(to_json($a), '$' || repeat('[0]', CAST($d - 1 AS INTEGER))) END AS INTEGER)
| | | |
array_fill
An array of a value repeated to the dimensions given. | array_fill($v::anyelement, $dims::integer[])
=>
CASE WHEN $dims IS NULL THEN error('postgresql-error 22004: ' || 'dimension array or low bound array cannot be null') WHEN len($dims) = 1 THEN list_transform(range($dims[1]), _i -> $v) ELSE error('postgresql-error 0A000: ' || 'array_fill does not read this value') END
array_fill($v::anyelement, $dims::integer[], $lower_bounds::integer[])
=>
CASE WHEN $dims IS NULL OR $lower_bounds IS NULL THEN error('postgresql-error 22004: ' || 'dimension array or low bound array cannot be null') WHEN len($dims) = 1 AND list_filter($lower_bounds, _b -> _b <> 1) = [] THEN list_transform(range($dims[1]), _i -> $v) ELSE error('postgresql-error 0A000: ' || 'array_fill does not read this value') END
| - Only one dimension with a lower bound of 1: any other shape is refused.
0A000 array_fill does not read this value
| | |
array_eq
Two arrays equal, a NULL element equal to a NULL element. | array_eq($a::anyarray, $b::anyarray)
=>
$a = $b
| | | |
array_ne
Two arrays not equal, a NULL element equal to a NULL element. | array_ne($a::anyarray, $b::anyarray)
=>
$a <> $b
| | | |
array_position
The position of the first element equal to a value, a NULL equal to a NULL; NULL for none. | array_position($a::anycompatiblearray, $v::anycompatible, $s::integer)
=>
list_position($a[greatest($s, 1):], $v) + greatest($s, 1) - 1
| | | |
array_positions
The positions of every element equal to a value, a NULL equal to a NULL. | array_positions($a::anycompatiblearray, $v::anycompatible)
=>
CAST(list_filter(range(1, len($a) + 1), _i -> $a[_i] IS NOT DISTINCT FROM $v) AS INTEGER[])
| | | |
array_replace
Each element equal to a value replaced by another, a NULL equal to a NULL. | array_replace($a::anycompatiblearray, $v::anycompatible, $w::anycompatible)
=>
list_transform($a, _x -> CASE WHEN _x IS NOT DISTINCT FROM $v THEN $w ELSE _x END)
| | | |
array_sample
A number of an array's elements, chosen at random. | array_sample($a::anyarray, $n::integer)
=>
CASE WHEN $a IS NULL OR $n IS NULL THEN NULL WHEN $n < 0 OR $n > len($a) THEN error('postgresql-error 22023: ' || 'sample size must be between 0 and ' || len($a)) ELSE list_transform(list_sort(list_transform($a, _x -> {'k': random(), 'v': _x})), _e -> _e.v)[1:$n] END
| | | |
array_shuffle
An array's elements in a random order. | array_shuffle($a::anyarray)
=>
list_transform(list_sort(list_transform($a, _x -> {'k': random(), 'v': _x})), _e -> _e.v)
| | | |
trim_array
An array without its last elements. | trim_array($a::anyarray, $n::integer)
=>
CASE WHEN $a IS NULL OR $n IS NULL THEN NULL WHEN $n < 0 OR $n > len($a) THEN error('postgresql-error 2202E: ' || 'number of elements to trim must be between 0 and ' || len($a)) ELSE $a[1:len($a) - $n] END
| | | |
generate_subscripts
The subscripts of a dimension, as rows, in either order. | generate_subscripts($a::anyarray, $d::integer, $reverse::boolean)
=>
unnest(CASE WHEN $reverse THEN range(CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER) THEN json_array_length(to_json($a), '$' || repeat('[0]', CAST($d - 1 AS INTEGER))) END AS INTEGER), 0, -1) ELSE range(1, CAST(CASE WHEN $d BETWEEN 1 AND CAST(CASE WHEN len($a) > 0 THEN length(regexp_extract(typeof($a), '(\[\])+$')) // 2 END AS INTEGER) THEN json_array_length(to_json($a), '$' || repeat('[0]', CAST($d - 1 AS INTEGER))) END AS INTEGER) + 1) END)
generate_subscripts($a::anyarray, $d::integer)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
array_cat
Two arrays concatenated; a NULL array is the other one, and two NULL arrays are NULL. | array_cat(NULL::anycompatiblearray, $b::anycompatiblearray)
=>
$b
array_cat($a::anycompatiblearray, NULL::anycompatiblearray)
=>
$a
array_cat($a::anycompatiblearray, $b::anycompatiblearray)
=>
CASE WHEN $a IS NOT NULL OR $b IS NOT NULL THEN list_concat($a, $b) END
| | | |
array_lt
One array before another, a NULL element after any value. | array_lt($a::anyarray, $b::anyarray)
=>
$a < $b
| | | |
array_le
One array before or equal to another, a NULL element after any value. | array_le($a::anyarray, $b::anyarray)
=>
$a <= $b
| | | |
array_gt
One array after another, a NULL element after any value. | array_gt($a::anyarray, $b::anyarray)
=>
$a > $b
| | | |
array_ge
One array after or equal to another, a NULL element after any value. | array_ge($a::anyarray, $b::anyarray)
=>
$a >= $b
| | | |
array_larger
The larger of two arrays, a NULL element after any value. | array_larger($a::anyarray, $b::anyarray)
=>
CASE WHEN $a > $b THEN $a WHEN $a <= $b THEN $b END
| | | |
array_smaller
The smaller of two arrays, a NULL element after any value. | array_smaller($a::anyarray, $b::anyarray)
=>
CASE WHEN $a < $b THEN $a WHEN $a >= $b THEN $b END
| | | |
btarraycmp
btree's comparison of two arrays: -1, 0 or 1, a NULL element after any value. | btarraycmp($a::anyarray, $b::anyarray)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
pg_timezone_names
The zones of the tz database, each with its abbreviation, offset and daylight flag now. | pg_timezone_names()
=>
function provisa.transpiler.polyfill.zones.zone_names: The pinned release's zones each read now: abbreviation, offset and daylight flag.
| | | |
timezone
A value at a zone; a zone written as a POSIX offset ('+05:30') is that many hours west of UTC. | timezone($z::text, $v)
=>
function provisa.transpiler.polyfill.zones.posix_zone: A zone's text PostgreSQL reads as a POSIX zone of a fixed offset, as the ICU zone of it.
| | | |
record_eq
Two records equal, a NULL field equal to a NULL field. | record_eq($a, $b)
=>
($a) = ($b)
| | | |
record_ne
Two records not equal, a NULL field equal to a NULL field. | record_ne($a, $b)
=>
($a) <> ($b)
| | | |
record_image_eq
Two records of equal images, a NULL field equal to a NULL field. | record_image_eq($a, $b)
=>
($a) = ($b)
| | | |
record_image_ne
Two records of images not equal, a NULL field equal to a NULL field. | record_image_ne($a, $b)
=>
($a) <> ($b)
| | | |
record_lt
One record before another. | record_lt($a, $b)
=>
($a) < ($b)
| | | |
record_le
One record before or equal to another. | record_le($a, $b)
=>
($a) <= ($b)
| | | |
record_gt
One record after another. | record_gt($a, $b)
=>
($a) > ($b)
| | | |
record_ge
One record after or equal to another. | record_ge($a, $b)
=>
($a) >= ($b)
| | | |
record_image_lt
One record image before another. | record_image_lt($a, $b)
=>
($a) < ($b)
| | | |
record_image_le
One record image before or equal to another. | record_image_le($a, $b)
=>
($a) <= ($b)
| | | |
record_image_gt
One record image after another. | record_image_gt($a, $b)
=>
($a) > ($b)
| | | |
record_image_ge
One record image after or equal to another. | record_image_ge($a, $b)
=>
($a) >= ($b)
| | | |
money_text
money's text, as cash_out writes it: $1,234.50, NULL for a NULL. | money_text($v)
=>
CASE WHEN CAST(round(($v) * 100, 0) AS BIGINT) < 0 THEN '-' ELSE '' END || '$' || format('{:,}', abs(CAST(CAST(round(($v) * 100, 0) AS BIGINT) AS HUGEINT)) // 100) || '.' || printf('%02d', CAST(abs(CAST(CAST(round(($v) * 100, 0) AS BIGINT) AS HUGEINT)) % 100 AS INTEGER))
| | | |
pg_collation_for
The collation a COLLATE clause gives a value, as quote_ident writes its name. | pg_collation_for($v::text COLLATE "default")
=>
'"default"'
pg_collation_for($v::character COLLATE "default")
=>
'"default"'
pg_collation_for($v::name COLLATE "default")
=>
'"default"'
pg_collation_for($v::text COLLATE "C")
=>
'"C"'
pg_collation_for($v::character COLLATE "C")
=>
'"C"'
pg_collation_for($v::name COLLATE "C")
=>
'"C"'
pg_collation_for($v::text COLLATE "POSIX")
=>
'"POSIX"'
pg_collation_for($v::character COLLATE "POSIX")
=>
'"POSIX"'
pg_collation_for($v::name COLLATE "POSIX")
=>
'"POSIX"'
pg_collation_for($v::text COLLATE "ucs_basic")
=>
'ucs_basic'
pg_collation_for($v::character COLLATE "ucs_basic")
=>
'ucs_basic'
pg_collation_for($v::name COLLATE "ucs_basic")
=>
'ucs_basic'
pg_collation_for($v::text COLLATE ucs_basic)
=>
'ucs_basic'
pg_collation_for($v::character COLLATE ucs_basic)
=>
'ucs_basic'
pg_collation_for($v::name COLLATE ucs_basic)
=>
'ucs_basic'
pg_collation_for($v::text COLLATE "unicode")
=>
'unicode'
pg_collation_for($v::character COLLATE "unicode")
=>
'unicode'
pg_collation_for($v::name COLLATE "unicode")
=>
'unicode'
pg_collation_for($v::text COLLATE unicode)
=>
'unicode'
pg_collation_for($v::character COLLATE unicode)
=>
'unicode'
pg_collation_for($v::name COLLATE unicode)
=>
'unicode'
pg_collation_for($v::text COLLATE "pg_c_utf8")
=>
'pg_c_utf8'
pg_collation_for($v::character COLLATE "pg_c_utf8")
=>
'pg_c_utf8'
pg_collation_for($v::name COLLATE "pg_c_utf8")
=>
'pg_c_utf8'
pg_collation_for($v::text COLLATE pg_c_utf8)
=>
'pg_c_utf8'
pg_collation_for($v::character COLLATE pg_c_utf8)
=>
'pg_c_utf8'
pg_collation_for($v::name COLLATE pg_c_utf8)
=>
'pg_c_utf8'
pg_collation_for($v COLLATE $c)
=>
refused (its error is under the limitations)
| - Refused: pg_collation_for($v COLLATE $c).
0A000 pg_collation_for does not read this value - Only the built-in collations (default, C, POSIX, ucs_basic, unicode, pg_c_utf8) of a text, character or name value: any other COLLATE is refused.
0A000 pg_collation_for does not read this value
| | |
ts_delete
A vector without the lexemes a list holds; a NULL item of the list is skipped. | ts_delete_array($v, $x)
=>
CASE WHEN $v IS NULL THEN NULL ELSE CASE WHEN CASE WHEN coalesce(NOT regexp_matches($v, '\A(?:''(?:[^'']|'''')*''(?::[0-9]+[A-C]?(?:,[0-9]+[A-C]?)*)?(?: ''(?:[^'']|'''')*''(?::[0-9]+[A-C]?(?:,[0-9]+[A-C]?)*)?)*)?\z'), false) THEN CAST(CAST('postgresql-error 0A000: ts_delete does not read this value <end of postgresql-error>' AS INTEGER) AS INTEGER) ELSE 0 END = 0 THEN array_to_string(list_filter(regexp_extract_all($v, '''(?:[^'']|'''')*''(?::[0-9]+[A-C]?(?:,[0-9]+[A-C]?)*)?'), _t -> NOT list_contains($x, replace(replace(regexp_extract(_t, '\A''((?:[^'']|'''')*)''', 1), '''''', ''''), '\\', '\'))), ' ') END END
| | | |
macaddr_and
The bitwise AND of two MAC addresses. | macaddr_and($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
macaddr_or
The bitwise OR of two MAC addresses. | macaddr_or($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
macaddr_not
The bitwise NOT of a MAC address. | macaddr_not($a)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
trunc
A MAC address with its last three (macaddr) or five (macaddr8) bytes set to zero. | trunc($m)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int2_sum
A step of an integer sum, in a bigint that wraps past its range. | int2_sum($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int4_sum
A step of an integer sum, in a bigint that wraps past its range. | int4_sum($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int8shl
A bigint shifted left, its count taken modulo 64, the result wrapping. | int8shl($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int2shr
A smallint shifted right, its sign kept, its count taken modulo 32. | int2shr($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int4shr
A integer shifted right, its sign kept, its count taken modulo 32. | int4shr($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
int8shr
A bigint shifted right, its sign kept, its count taken modulo 64. | int8shr($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
factorial
The product of the integers from 1 to n. | factorial($n)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
numeric
An integer as a numeric. | numeric($v)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
bool
An integer as a truth value: false for 0. | bool($v)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
oid
A bigint as an oid. | oid($v)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
random
A uniform random integer between two bounds, both included. | random($lo, $hi)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
date_mi
The days from one date to another. | date_mi($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
date_pli
A date moved forward by a number of days. | date_pli($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
date_mii
A date moved back by a number of days. | date_mii($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
make_date
A date of a year, a month and a day. | make_date($y, $m, $d)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
transaction_timestamp
The time the transaction began: now(), as Provisa answers it. | transaction_timestamp()
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
mxid_age
The age of a multixact id: INT_MAX for 0, and 1 less it, as Provisa makes no multixact. | mxid_age($x)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
pg_char_to_encoding
An encoding's number by any of PostgreSQL's names of it; -1 for a name of none. | pg_char_to_encoding($n)
=>
CASE WHEN $n IS NULL THEN CAST(NULL AS INTEGER) WHEN strlen($n) > 63 THEN error('postgresql-error 0A000: pg_char_to_encoding does not read this value<end of postgresql-error>') ELSE CAST(CASE lower(regexp_replace($n, '[^A-Za-z0-9]', '', 'g')) WHEN 'abc' THEN 19 WHEN 'alt' THEN 20 WHEN 'big5' THEN 36 WHEN 'euccn' THEN 2 WHEN 'eucjis2004' THEN 5 WHEN 'eucjp' THEN 1 WHEN 'euckr' THEN 3 WHEN 'euctw' THEN 4 WHEN 'gb18030' THEN 39 WHEN 'gbk' THEN 37 WHEN 'iso88591' THEN 8 WHEN 'iso885910' THEN 13 WHEN 'iso885913' THEN 14 WHEN 'iso885914' THEN 15 WHEN 'iso885915' THEN 16 WHEN 'iso885916' THEN 17 WHEN 'iso88592' THEN 9 WHEN 'iso88593' THEN 10 WHEN 'iso88594' THEN 11 WHEN 'iso88595' THEN 25 WHEN 'iso88596' THEN 26 WHEN 'iso88597' THEN 27 WHEN 'iso88598' THEN 28 WHEN 'iso88599' THEN 12 WHEN 'johab' THEN 40 WHEN 'koi8' THEN 22 WHEN 'koi8r' THEN 22 WHEN 'koi8u' THEN 34 WHEN 'latin1' THEN 8 WHEN 'latin10' THEN 17 WHEN 'latin2' THEN 9 WHEN 'latin3' THEN 10 WHEN 'latin4' THEN 11 WHEN 'latin5' THEN 12 WHEN 'latin6' THEN 13 WHEN 'latin7' THEN 14 WHEN 'latin8' THEN 15 WHEN 'latin9' THEN 16 WHEN 'mskanji' THEN 35 WHEN 'muleinternal' THEN 7 WHEN 'shiftjis' THEN 35 WHEN 'shiftjis2004' THEN 41 WHEN 'sjis' THEN 35 WHEN 'sqlascii' THEN 0 WHEN 'tcvn' THEN 19 WHEN 'tcvn5712' THEN 19 WHEN 'uhc' THEN 38 WHEN 'unicode' THEN 6 WHEN 'utf8' THEN 6 WHEN 'vscii' THEN 19 WHEN 'win' THEN 23 WHEN 'win1250' THEN 29 WHEN 'win1251' THEN 23 WHEN 'win1252' THEN 24 WHEN 'win1253' THEN 30 WHEN 'win1254' THEN 31 WHEN 'win1255' THEN 32 WHEN 'win1256' THEN 18 WHEN 'win1257' THEN 33 WHEN 'win1258' THEN 19 WHEN 'win866' THEN 20 WHEN 'win874' THEN 21 WHEN 'win932' THEN 35 WHEN 'win936' THEN 37 WHEN 'win949' THEN 38 WHEN 'win950' THEN 36 WHEN 'windows1250' THEN 29 WHEN 'windows1251' THEN 23 WHEN 'windows1252' THEN 24 WHEN 'windows1253' THEN 30 WHEN 'windows1254' THEN 31 WHEN 'windows1255' THEN 32 WHEN 'windows1256' THEN 18 WHEN 'windows1257' THEN 33 WHEN 'windows1258' THEN 19 WHEN 'windows866' THEN 20 WHEN 'windows874' THEN 21 WHEN 'windows932' THEN 35 WHEN 'windows936' THEN 37 WHEN 'windows949' THEN 38 WHEN 'windows950' THEN 36 ELSE -1 END AS INTEGER) END
| - A name of more than 63 bytes is refused by name where it runs: PostgreSQL cuts it to them as it reads it.
0A000 pg_char_to_encoding does not read this value
| | |
ntile
The bucket of a row in a window, as an integer. | function provisa.transpiler.polyfill.clickhouse.bucketed: ntile over a window, as PostgreSQL's integer: every engine's is a bigint. On ClickHouse a
| | | |
numeric_mod
The remainder of one numeric divided by another, at the larger scale of the two. | numeric_mod($a, $b)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |
spg_poly_quad_compress
A polygon's bounding box, as the SP-GiST quad tree's compress function answers it. | spg_poly_quad_compress($v)
=>
function provisa.transpiler.polyfill.trino.kept: The old table's form (provisa.transpiler.function_macros): this row writes Trino's only.
| | | |