v0.189

Add support for dereferencing row fields in lambda expressions

The following lambda function which uses a table alias is supported.

select transform(array[1], x -> x + t.a) from (values 1, 2, 3) t(a);
=> 
["2"]
["3"]
["4"]

v0.190

Add support for ORDER BY in aggregation functions

Some aggregate functions such as array_agg() produce different results depending on the order of input values. This ordering can be specified by writing an ORDER BY Clause within the aggregate function:

array_agg(x ORDER BY y DESC)
array_agg(x ORDER BY x, y, z) 

Add support for casting to INTERVAL types

Optimized aggregations on INTERVAL types

SELECT CAST(INTERVAL '18000' SECOND AS VARCHAR);
=>0 05:00:00.000

Add ST_Buffer() geospatial function

Returns the geometry that represents all points whose distance from the specified geometry is less than or equal to the specified distance.

SELECT ST_BUFFER(ST_Point(1, 2), 2.0) 
=> POLYGON ((3 2, 2.9957178464772056 2.130806258460286, 2.982889722747619 2.2610523844401027, 2.9615705608064595 2.390180644032256, 2.9318516525781355 2.517638090205041, 2.8938602589902103 2.6428789306063227, 2.8477590650225726 2.765366864730179, 2.7937454830653756 2.8845773804380017, 2.7320508075688767 2.999999999999999, 2.66293922460509 3.1111404660392035, 2.58670668058247 3.217522858017441, 2.5036796149579543 3.318691630200137, 2.414213562373095 3.4142135623730945, 2.3186916302001377 3.5036796149579543, 2.217522858017441 3.58670668058247, 2.1111404660392044 3.66293922460509, 2 3.7320508075688767, 1.8845773804380026 3.7937454830653765, 1.7653668647301795 3.847759065022573, 1.6428789306063232 3.893860258990211, 1.5176380902050415 3.9318516525781364, 1.3901806440322564 3.961570560806461, 1.2610523844401031 3.982889722747621, 1.130806258460286 3.995717846477207, 1 4, 0.8691937415397142 3.9957178464772056, 0.7389476155598972 3.982889722747619, 0.6098193559677438 3.9615705608064595, 0.48236190979495897 3.9318516525781355, 0.3571210693936774 3.8938602589902103, 0.2346331352698211 3.8477590650225726, 0.11542261956199817 3.7937454830653756, 7.7715611723760960e-16 3.7320508075688767, -0.11114046603920369 3.66293922460509, -0.21752285801744065 3.58670668058247, -0.318691630200137 3.5036796149579543, -0.4142135623730945 3.414213562373095, -0.5036796149579543 3.3186916302001377, -0.5867066805824699 3.217522858017441, -0.66293922460509 3.1111404660392044, -0.732050807568877 3, -0.7937454830653763 2.8845773804380026, -0.8477590650225733 2.7653668647301792, -0.8938602589902112 2.642878930606323, -0.9318516525781366 2.5176380902050415, -0.9615705608064609 2.3901806440322564, -0.9828897227476208 2.261052384440103, -0.995717846477207 2.1308062584602863, -1 2, -0.9957178464772056 1.8691937415397142, -0.9828897227476194 1.7389476155598973, -0.9615705608064595 1.6098193559677438, -0.9318516525781353 1.482361909794959, -0.8938602589902103 1.3571210693936773, -0.8477590650225726 1.2346331352698212, -0.7937454830653756 1.1154226195619983, -0.7320508075688765 1.0000000000000009, -0.6629392246050898 0.8888595339607963, -0.5867066805824699 0.7824771419825594, -0.5036796149579543 0.681308369799863, -0.4142135623730947 0.5857864376269055, -0.31869163020013747 0.4963203850420457, -0.2175228580174411 0.4132933194175301, -0.11114046603920436 0.33706077539491, 1.1102230246251565e-16 0.26794919243112303, 0.11542261956199751 0.2062545169346237, 0.23463313526982055 0.15224093497742675, 0.35712106939367694 0.10613974100978885, 0.4823619097949585 0.06814834742186338, 0.6098193559677435 0.03842943919353914, 0.7389476155598969 0.017110277252379236, 0.8691937415397138 0.004282153522793042, 1 0, 1.1308062584602858 0.004282153522794374, 1.2610523844401027 0.017110277252380568, 1.3901806440322562 0.03842943919354047, 1.517638090205041 0.06814834742186471, 1.6428789306063227 0.10613974100978973, 1.7653668647301788 0.1522409349774274, 1.8845773804380017 0.20625451693462438, 1.9999999999999991 0.2679491924311235, 2.1111404660392035 0.3370607753949102, 2.217522858017441 0.4132933194175301, 2.318691630200137 0.4963203850420457, 2.4142135623730945 0.5857864376269053, 2.5036796149579543 0.6813083697998625, 2.58670668058247 0.7824771419825589, 2.66293922460509 0.8888595339607956, 2.7320508075688767 1, 2.7937454830653765 1.1154226195619974, 2.847759065022573 1.2346331352698205, 2.893860258990211 1.3571210693936768, 2.9318516525781364 1.4823619097949585, 2.961570560806461 1.6098193559677436, 2.982889722747621 1.7389476155598969, 2.995717846477207 1.869193741539714, 3 2))

approx_distinct()function now supports a standard error in the range of [0.0040625, 0.26000]

Returns the approximate number of distinct input values. This function provides an approximation of count(DISTINCT x). Zero is returned if all input values are null.

This function should produce a standard error of no more than e, which is the standard deviation of the (approximately normal) error distribution over all possible sets. It does not guarantee an upper bound on the error for any specific input set. The current implementation of this function requires that e be in the range of [0.0040625, 0.26000].

SELECT APPROX_DISTINCT(x, value) from tbl;

v0.193

Add map_zip_with() lambda function

Merges the two given maps into a single map by applying function to the pair of values with the same key. For keys only presented in one map, NULL will be passed as the value for the missing key.

map_zip_with(map<K, V1>, map<K, V2>, function<K, V1, V2, V3>) → map<K, V3>

SELECT map_zip_with(MAP(ARRAY[1, 2, 3], ARRAY['a', 'b', 'c']), -- {1 -> ad, 2 -> be, 3 -> cf} 
                    MAP(ARRAY[1, 2, 3], ARRAY['d', 'e', 'f']), 
                    (k, v1, v2) -> concat(v1, v2)); 

SELECT map_zip_with(MAP(ARRAY['k1', 'k2'], ARRAY[1, 2]), -- {k1 -> ROW(1, null), k2 -> ROW(2, 4), k3 -> ROW(null, 9)} 
                    MAP(ARRAY['k2', 'k3'], ARRAY[4, 9]), 
                    (k, v1, v2) -> (v1, v2)); 

SELECT map_zip_with(MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 8, 27]), -- {a -> a1, b -> b4, c -> c9} 
                    MAP(ARRAY['a', 'b', 'c'], ARRAY[1, 2, 3]), 
                    (k, v1, v2) -> k || CAST(v1/v2 AS VARCHAR));

Add normal_cdf() function

Returns the cumulative distribution function value for a normal distribution.

Normal_cdf(mean, standard deviation, value) → double

v0.196

Add is_json_scalar() function

Determine if json is a scalar (i.e. a JSON number, a JSON string, true,false or null):

SELECT is_json_scalar('1'); 
=> true 

SELECT is_json_scalar('[1, 2, 3]'); 
=> false

Add regexp_replace()function variant that executes a lambda for each replacement

Replaces every instance of the substring matched by the regular expression pattern in string with replacement. Capturing groups can be referenced in replacement using $g for a numbered group or ${name} for a named group. A dollar sign ($) may be included in the replacement by escaping it with a backslash (\$):

SELECT regexp_replace('1a 2b 14m', '(\d+)([ab]) ', '3c$2 '); 
=> '3ca 3cb 14m'

v0.197 and v0.198

Add DATE variant for sequence()function with an implicit one-day step increment

Generate a sequence of integers from start to stop, incrementing by 1 if start is less than or equal to stop,otherwise -1.

v0.197

Add ST_IsSimple() geospatial function

SELECT ST_IsSimple(ST_GeomFromText('POLYGON((1 2, 3 4, 5 6, 1 2))')); 
st_issimple 
------------- 
t 
(1 row) 

SELECT ST_IsSimple(ST_GeomFromText('LINESTRING(1 1,2 2,2 3.5,1 3,1 2,2 1)')); 
st_issimple 
------------- 
f 
(1 row)

v0.199

Add hmac_md5(), hmac_sha1(), hmac_sha256(), and hmac_sha512()functions

Computes HMAC with md5 or sha1 or sha256 or sha512 of binary with the given key

hmac_md5(binary, key) → varbinaryComputes

hmac_sha1(binary, key) → varbinaryComputes 

hmac_sha256(binary, key) → varbinaryComputes 

hmac_sha512(binary, key) → varbinaryComputes 

Add array_sort() function that takes a lambda as a comparator

Sorts and returns the array based on the given comparator function. The comparator will take two nullable arguments representing two nullable elements of the array. It returns -1, 0, or 1 as the first nullable element is less than, equal to, or greater than the second nullable element. If the comparator function returns other values (including NULL), the query will fail and raise an error

SELECT array_sort(ARRAY [3, 2, 5, 1, 2], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- [5, 3, 2, 2, 1] 
SELECT array_sort(ARRAY ['bc', 'ab', 'dc'], (x, y) -> IF(x < y, 1, IF(x = y, 0, -1))); -- ['dc', 'bc', 'ab'] 
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null first with descending order 
                 (x, y) -> CASE WHEN x IS NULL THEN -1 
                                WHEN y IS NULL THEN 1 
                                WHEN x < y THEN 1 
                                WHEN x = y THEN 0 
                                ELSE -1 END); -- [null, null, 5, 3, 2, 2, 1] 
SELECT array_sort(ARRAY [3, 2, null, 5, null, 1, 2], -- sort null last with descending order 
                 (x, y) -> CASE WHEN x IS NULL THEN 1 
                                WHEN y IS NULL THEN -1 
                                WHEN x < y THEN 1 
                                WHEN x = y THEN 0 
                                ELSE -1 END); -- [5, 3, 2, 2, 1, null, null] 
SELECT array_sort(ARRAY ['a', 'abcd', 'abc'], -- sort by string length 
                  (x, y) -> IF(length(x) < length(y), 
                            -1, 
                              IF(length(x) = length(y), 0, 1))); -- ['a', 'abc', 'abcd'] 
SELECT array_sort(ARRAY [ARRAY[2, 3, 1], ARRAY[4, 2, 1, 4], ARRAY[1, 2]], -- sort by array length 
                  (x, y) -> IF(cardinality(x) < cardinality(y), 
                             -1, 
                               IF(cardinality(x) = cardinality(y), 0, 1))); -- [[1, 2], [2, 3, 1], [4, 2, 1, 4]]

Add line_locate_point()geospatial function

Returns a float between 0 and 1 representing the location of the closest point on the LineString to the given Point, as a fraction of total 2d line length.

Returns null if a LineString or a Point is empty or null.

SELECT LINE_LOCATE_POINT(GEOMFROMTEXT('LINESTRING(0 0, 5 5, 10 10)'), GEOMFROMTEXT('POINT(5 5)'));

-------------
col1
-------------
0.500
-------------

v0.200

Add ST_GeometryType() geospatial function

Returns the type of the geometry.

SELECT ST_GeometryType(ST_GeomFromText('LINESTRING(77.29 29.07,77.42 29.26,77.27 29.31,77.29 29.07)'));

=> ST_LineString

v0.202

Add multimap_from_entries()function

Returns a map created from the given array of entries.

SELECT multimap_from_entries(ARRAY[(1, 'x'), (2, 'y'), (1, 'z')]); -- {1 -> ['x', 'z'], 2 -> ['y']}

Add bing_tiles_around()geospatial functions

Returns a collection of Bing tiles that surround the point specified by the latitude and longitude arguments at a given zoom level.

bing_tiles_around(latitude, longitude, zoom_level) → array<BingTile>

Add ST_NumGeometries() geospatial functions

Returns the number of geometries in the collection. If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the number of geometries, for single geometries returns 1, for empty geometries returns 0.

ST_NumGeometries(Geometry) → bigint

SELECT ST_NumGeometries(ST_GeomFromEWKT('GEOMETRYCOLLECTION(MULTIPOINT(-2 3 , -2 2), 
LINESTRING(5 5 ,10 10), 
POLYGON((-7 4.2,-7.1 5,-7.1 4.3,-7 4.2)))'));

Add ST_GeometryN() geospatial functions

Returns the geometry element at a given index (indices start at 1). If the geometry is a collection of geometries (e.g., GEOMETRYCOLLECTION or MULTI*), returns the geometry at a given index. If the given index is less than 1 or greater than the total number of elements in the collection, returns NULL.Use :func:ST_NumGeometries to find out the total number of elements. Singular geometries (e.g., POINT, LINESTRING, POLYGON), are treated as collections of one element. Empty geometries are treated as empty collections.

SELECT gid, n, ST_GeometryN(the_geom, n)
FROM tbl CROSS JOIN generate_series(1,100) n WHERE n <= ST_NumGeometries(the_geom);

Add ST_ConvexHull() geospatial functions

Returns the minimum convex geometry that encloses all input geometries. This function doesn’t support geometry collections.

SELECT t.tbl_type, ST_ConvexHull(ST_Collect(t.the_geom)) As the_geom 
FROM tbl As t 
GROUP BY t.tbl_type;

Add wilson_interval_lower() and wilson_interval_upper() functions

Returns the lower bound of the Wilson score interval of a Bernoulli trial process at a confidence specified by the z-score Z.

assertFunction("ST_AsText(ST_EnvelopeAsPts(ST_GeometryFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')))", 
VARCHAR, "MULTIPOINT ((1 2), (4 8))"); 

  • No labels