RE: search inside partitions
Thanks very much Torsten, it works like a charm. I was not used to recursive functions, that's a good beginning...
Nice week-end to all
De : Torsten Grust [mailto:torsten.grust@gmail.com]
Envoyé : vendredi 2 février 2018 12:20
À : pgsql-sql@lists.postgresql.org
Objet : Re: search inside partitions
Hi Olivier,
since this is some sort of "pointer chasing" through the table, I guess a recursive SQL function can do the trick.
See the SQL code below which it appears to do the job (modulo any false interpretation of your problem statement).
Cheers,
—Torsten
DROP TABLE IF EXISTS t;CREATE TABLE t ( a INT NOT NULL, b INT NOT NULL); INSERT INTO t(a,b) VALUES ( 298, 151208), (151208, 151209), (151210, 2000), ( 2000, 151211), ( 2004, 151213), ( 2004, 2013), ( 27601, 151214), (151214, 151215), (151212, 151213), ( 2016, 2018), (151215, 2016), ( 2018, 2019), ( 2019, 2020), ( 2020, 151217), (151217, 151218), ( 2026, 2028), ( 33753, 2026), ( 2013, 27601), (151218, 151219), (151219, 33753), (151209, 151210), (151211, 151212); WITH RECURSIVE chase(a, b, pos) AS ( SELECT t.a, t.b, 1 AS pos FROM T AS t WHERE t.a = 298 -- identifies the "first row" in the input UNION ALL SELECT CASE WHEN c.b = t.a THEN t.a ELSE t.b END AS a, CASE WHEN c.b = t.a THEN t.b ELSE t.a END AS b, c.pos + 1 AS pos FROM chase AS c, T AS t WHERE (c.b = t.a AND c.a <> t.b) OR (c.b = t.b AND c.a <> t.a))TABLE chase;
On Fri, Feb 2, 2018 at 10:00 AM Olivier Leprêtre <o.lepretre@gmail.com> wrote:
Hi to all,
I have a table, from which I can extract partitions which need to be ordered, one after the other. A partition is composed of 2 columns A and B and ordering is done by finding the next value of B in another row of the partition.
Here are the two columns of one of the partitions :
298
151208
151208
151209
151210
2000
2000
151211
2004
151213
2004
2013
27601
151214
151214
151215
151212
151213
2016
2018
151215
2016
2018
2019
2019
2020
2020
151217
151217
151218
2026
2028
33753
2026
2013
27601
151218
151219
151219
33753
151209
151210
151211
151212
Starting from the first row the goal is to find the same value in previous of following rows, column A or B.
Example : starting from row 1, 15208 returns row 2 15208-15209, 15209 returns row 15209-15210, 15210 returns 15210-2000 and so on. When the next row is find, a third column C is incremented to be used for sorting
results should then be
298
151208
1
151208
151209
2
151209
151210
3
151210
2000
4
2000
151211
5
151211
151212
6
151212
151213
7
151213
2004
8
2004
2013
9
2013
27601
10
27601
151214
11
151214
151215
12
151215
2016
13
2016
2018
14
2018
2019
15
2019
2020
16
2020
151217
17
151217
151218
18
151218
151219
19
151219
33753
20
33753
2026
21
2026
2028
22
By the way, switching has to be done to have always previous B -> next A but this is less important as it can be done using tricks like putting a minus sign when required and switching afterwards.
I'm wondering if there is a rather simple way to do that using partition functions or if I should better try do that using C#
Thanks for any idea,
Olivier
Garanti sans virus. www.avast.com
--
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)
--
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)
Вложения
В списке pgsql-sql по дате отправления: