Goodmorning,
At this moment there is no functional compatibility between the PostgreSQL and MonetDB with respect to the generate_series function. The range in PostgreSQL is inclusive, in MonetDB exclusive. More importantly generate_series can't be used with argument that is joined from a different table, MonetDB just seems to support a subquery, hence joining that back without a primary is impossible.
PostgreSQL; select generate_series(a, b), a from (select 1 as a, 2 as b) as a; generate_series | a -----------------+--- 1 | 1 2 | 1 (2 rows)
MonetDB; select * from generate_series((select 1 as a, 2 as b)); +-------+ | value | +=======+ | 1 | +-------+
If I would like to functionally achieve the following;
I have list that states the speeds over partial segments, identified by linear referencing. From 10 meter to 12 meter, the distance was 2 meter and speed was 2s.
10 2 2
I would like to expand these values to segments of one meter, hence end up with a table that would state:
10 2 2 11 2 2 12 2 2
Now I am aware of the Python bindings and I could achieve what I wanted by the function below, but I would find generate_series still very practical.
DROP FUNCTION expand_per_meter; CREATE FUNCTION expand_per_meter(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) RETURNS TABLE(trip_hash BIGINT, userstopcode STRING, distancesincelastuserstop INTEGER, s INTEGER, t INTEGER) LANGUAGE PYTHON { result = dict() result['trip_hash'] = [] result['userstopcode'] = [] result['distancesincelastuserstop'] = [] result['s'] = [] result['t'] = [] for i in range(0, len(trip_hash)): expanded = range(distancesincelastuserstop[i], distancesincelastuserstop[i] + s[i] + 1) expand = len(expanded) result['trip_hash'] += [trip_hash[i]] * expand result['userstopcode'] += [userstopcode[i]] * expand result['distancesincelastuserstop'] += expanded result['s'] += [s[i]] * expand result['t'] += [t[i]] * expand return result };