joinable generate_series with arguments from a table

Torsten Grust torsten.grust at gmail.com
Wed Jun 13 09:42:41 CEST 2018


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 <stefan at konink.de> wrote:

> 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 at monetdb.org
> https://www.monetdb.org/mailman/listinfo/users-list
>
-- 
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)
| ✉︎ torsten.grust at uni-tuebingen.de
| db.inf.uni-tuebingen.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.monetdb.org/pipermail/users-list/attachments/20180613/4055cd94/attachment.html>


More information about the users-list mailing list