関数と演算子
本章ではGPUデバイス上で実行可能な関数と演算子について説明します。
型キャスト
destination type | source type | description |
---|---|---|
bool |
int4 |
|
int2 |
int4,int8,float2,float4,float8,numeric |
|
int4 |
int2,int8,float2,float4,float8,numeric |
|
int8 |
int2,int4,float2,float4,float8,numeric |
|
float2 |
int2,int4,int8,float4,float8,numeric |
|
float4 |
int2,int4,int8,float2,float8,numeric |
|
float8 |
int2,int4,int8,float2,float4,numeric |
|
numeric |
int2,int4,int8,float2,float4,float8 |
|
money |
int4,int8,numeric |
|
inet |
cidr |
|
date |
timestamp,timestamptz |
|
time |
timetz,timestamp,timestamptz |
|
timetz |
time,timestamptz |
|
timestamp |
date,timestamptz |
|
timestamptz |
date,timestamp |
数値型演算子
function/operator | description |
---|---|
TYPE COMP TYPE |
Comparison of two valuesTYPE is any of int2,int4,int8 COMP is any of =,<>,<,<=,>=,> |
TYPE COMP TYPE |
Comparison of two valuesTYPE is any of float2,float4,float8 COMP is any of =,<>,<,<=,>=,> |
numeric COMP numeric |
Comparison of two valuesCOMP is any of =,<>,<,<=,>=,> |
TYPE + TYPE |
Arithemetic additionTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE - TYPE |
Arithemetic substractTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE * TYPE |
Arithemetic multiplicationTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE / TYPE |
Arithemetic divisionTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
TYPE % TYPE |
Reminer operatorTYPE is any of int2,int4,int8 |
TYPE & TYPE |
Bitwise ANDTYPE is any of int2,int4,int8 |
TYPE | TYPE |
Bitwise ORTYPE is any of int2,int4,int8 |
TYPE # TYPE |
Bitwise XORTYPE is any of int2,int4,int8 |
~ TYPE |
Bitwise NOTTYPE is any if int2,int4,int8 |
TYPE >> int4 |
Right shiftTYPE is any of int2,int4,int8 |
TYPE << int4 |
Left shiftTYPE is any of int2,int4,int8 |
+ TYPE |
Unary plusTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
- TYPE |
Unary minusTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
@TYPE |
Absolute valueTYPE is any of int2,int4,int8,float2,float4,float8,numeric |
数学関数
functions/operators | description |
---|---|
cbrt(float8) |
cube root |
dcbrt(float8) |
cube root |
ceil(float8) |
nearest integer greater than or equal to argument |
ceiling(float8) |
nearest integer greater than or equal to argument |
exp(float8) |
exponential |
dexp(float8) |
exponential |
floor(float8) |
nearest integer less than or equal to argument |
ln(float8) |
natural logarithm |
dlog1(float8) |
natural logarithm |
log(float8) |
base 10 logarithm |
dlog10(float8) |
base 10 logarithm |
pi() |
circumference ratio |
power(float8,float8) |
power |
pow(float8,float8) |
power |
dpow(float8,float8) |
power |
round(float8) |
round to the nearest integer |
dround(float8) |
round to the nearest integer |
sign(float8) |
sign of the argument |
sqrt(float8) |
square root |
dsqrt(float8) |
square root |
trunc(float8) |
truncate toward zero |
dtrunc(float8) |
truncate toward zero |
三角関数
functions/operators | description |
---|---|
degrees(float8) |
radians to degrees |
radians(float8) |
degrees to radians |
acos(float8) |
inverse cosine |
asin(float8) |
inverse sine |
atan(float8) |
inverse tangent |
atan2(float8,float8) |
inverse tangent of arg1 / arg2 |
cos(float8) |
cosine |
cot(float8) |
cotangent |
sin(float8) |
sine |
tan(float8) |
tangent |
日付/時刻型演算子
functions/operators | description |
---|---|
date COMP date |
COMP is any of =,<>,<,<=,>=,> |
date COMP timestamp |
COMP is any of =,<>,<,<=,>=,> |
date COMP timestamptz |
COMP is any of =,<>,<,<=,>=,> |
time COMP time |
COMP is any of =,<>,<,<=,>=,> |
timetz COMP timetz |
COMP is any of =,<>,<,<=,>=,> |
timestamp COMP timestamp |
COMP is any of =,<>,<,<=,>=,> |
timestamp COMP date |
COMP is any of =,<>,<,<=,>=,> |
timestamptz COMP timestamptz |
COMP is any of =,<>,<,<=,>=,> |
timestamptz COMP date |
COMP is any of =,<>,<,<=,>=,> |
timestamp COMP timestamptz |
COMP is any of =,<>,<,<=,>=,> |
timestamptz COMP timestamp |
COMP is any of =,<>,<,<=,>=,> |
interval COMP interval |
COMP is any of =,<>,<,<=,>=,> |
date OP int4 |
OP is either of +,- |
int4 + date |
|
date - date |
|
date + time |
|
date + timetz |
|
time + date |
|
time - time |
|
timestamp - timestamp |
|
timetz OP interval |
OP is either of +,- |
timestamptz OP interval |
OP is either of +,- |
overlaps(TYPE,TYPE,TYPE,TYPE) |
TYPE is any of time,timetz,timestamp,timestamptz |
extract(text FROM TYPE) |
TYPE is any of time,timetz,timestamp,timestamptz,interval |
now() |
|
- interval |
unary minus operator |
interval OP interval |
OP is either of +,- |
文字列関数/演算子
functions/operators | description |
---|---|
{text,bpchar} COMP {text,bpchar} |
COMP is either of =,<> |
{text,bpchar} COMP {text,bpchar} |
COMP is either of <,<=,>=,> Only available on no-locale or UTF-8 |
varchar || varchar |
Both side must be varchar(n) with maximum length. |
substring , substr |
|
length(TYPE) |
length of the stringTYPE is either of text,bpchar |
TYPE LIKE text |
TYPE is either of text,bpchar |
TYPE NOT LIKE text |
TYPE is either of text,bpchar |
TYPE ILIKE text |
TYPE is either of text,bpchar Only available on no-locale or UTF-8 |
TYPE NOT ILIKE text |
TYPE is either of text,bpchar Only available on no-locale or UTF-8 |
ネットワーク関数/演算子
functions/operators | description |
---|---|
macaddr COMP macaddr |
COMP is any of =,<>,<,<=,>=,> |
macaddr & macaddr |
Bitwise AND operator |
macaddr | macaddr |
Bitwise OR operator |
~ macaddr |
Bitwise NOT operator |
trunc(macaddr) |
Set last 3 bytes to zero |
inet COMP inet |
COMP is any of =,<>,<,<=,>=,> |
inet INCL inet |
INCL is any of <<,<<=,>>,>>=,&& |
~ inet |
|
inet & inet |
|
inet | inet |
|
inet + int8 |
|
inet - int8 |
|
inet - inet |
|
broadcast(inet) |
|
family(inet) |
|
hostmask(inet) |
|
masklen(inet) |
|
netmask(inet) |
|
network(inet) |
|
set_masklen(cidr,int) |
|
set_masklen(inet,int) |
|
inet_same_family(inet, inet) |
|
inet_merge(inet,inet) |
通貨型演算子
functions/operators | description |
---|---|
money COMP money |
COMP is any of =,<>,<,<=,>=,> |
money OP money |
OP is any of +,-,/ |
money * TYPE |
TYPE is any of int2,int4,float2,float4,float8 |
TYPE * money |
TYPE is any of int2,int4,float2,float4,float8 |
money / TYPE |
TYPE is any of int2,int4,float2,float4,float8 |
uuid型演算子
functions/operators | description |
---|---|
uuid COMP uuid |
COMP is any of =,<>,<,<=,>=,> |
JSONB型演算子
functions/operators | description |
---|---|
jsonb -> KEY |
Get a JSON object field specified by the KEY |
jsonb -> NUM |
Get a JSON array element indexed by NUM |
jsonb ->> KEY |
Get a JSON object field specified by the KEY , as text |
jsonb ->> NUM |
Get a JSON array element indexed by NUM |
(jsonb ->> KEY)::TYPE |
TYPE is any of int2,int4,int8,float4,float8,numeric Get a JSON object field specified by KEY , as numeric data type. See the note below. |
(jsonb ->> NUM)::TYPE |
TYPE is any of int2,int4,int8,float4,float8,numeric Get a JSON array element indexed by NUM , as numeric data type. See the note below. |
jsonb ? KEY |
Check whether jsonb object contains the KEY |
Note
jsonb ->> KEY
演算子によって取り出した数値データをfloat
やnumeric
など数値型に変換する時、通常、PostgreSQLはjsonb内部表現をテキストとして出力し、それを数値表現に変換するという2ステップの処理を行います。
PG-Stromはjsonb ->> KEY
演算子による参照とテキスト⇒数値表現へのキャストが連続している時、jsonbオブジェクトから数値表現を取り出すための特別なデバイス関数を使用する事で最適化を行います。
範囲型演算子
functions/operators | description |
---|---|
RANGE = RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE <> RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE < RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE <= RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE > RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE >= RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE @ RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE @ TYPE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange TYPE is element type of RANGE . |
RANGE <@RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
TYPE <@RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange TYPE is element type of RANGE . |
RANGE && RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE << RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE >> RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE &< RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE &> RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE -|- RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE + RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE * RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
RANGE - RANGE |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
lower(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
upper(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
isempty(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
lower_inc(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
upper_inc(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
lower_inf(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
upper_inf(RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
range_merge(RANGE,RANGE) |
RANGE is any of int4range,int8range,tsrange,tstzrange,daterange |
その他のデバイス関数
functions/operators | result | description |
---|---|---|
as_int8(float8) |
int8 |
Re-interpret double-precision floating point bit-pattern as 64bit integer value |
as_int4(float4) |
int4 |
Re-interpret single-precision floating point bit-pattern as 32bit integer value |
as_int2(float2) |
int2 |
Re-interpret half-precision floating point bit-pattern as 16bit integer value |
as_float8(int8) |
float8 |
Re-interpret 64bit integer bit-pattern as double-precision floating point value |
as_float4(int4) |
float4 |
Re-interpret 32bit integer bit-pattern as single-precision floating point value |
as_float2(int2) |
float2 |
Re-interpret 16bit integer bit-pattern as half-precision floating point value |