joinable generate_series with arguments from a table

Stefan de Konink stefan at konink.de
Tue Jun 12 09:24:35 CEST 2018


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


More information about the users-list mailing list