Hi Stefan,
note that the use of generate_series() in the SELECT clause is deprecated
by the PostgreSQL folks. That functionality may go away in future releases.
An alternative formulation of your segment expansion query would be the
following (I assume that your input table is segments(meter, distance,
speed)):
SELECT t.meter, s.distance, s.speed
FROM segments AS s, LATERAL generate_series(s.meter,
s.meter+s.distance+1) AS t(meter);
Note the use of LATERAL such that in the invocation of generate_series() we
may refer to row variable s.
Now, as I understand it, this query should work in MonetDB. LATERAL support
is in MonetDB and has received recent bug fixes. The query work does
*not* work,
though: MonetDB 5 server v11.27.13 "Jul2017-SP4" (on macOS Sierra 10.12.6)
goes into an infinite loop already when I try to plan the query. Which
turns this reply into a bug report, I guess. ¯\_(ツ)_/¯
Best wishes,
—Torsten
On Tue, Jun 12, 2018 at 9:33 AM Stefan de Konink
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 };
-- Stefan _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- | Prof. Dr. Torsten Grust | Database Systems — Universität Tübingen (Germany) | ✉︎ torsten.grust@uni-tuebingen.de | db.inf.uni-tuebingen.de