agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Olivier Leprêtre <o.lepretre@gmail.com>
To: 'Torsten Grust' <torsten.grust@gmail.com>
To: pgsql-sql@lists.postgresql.org
Subject: RE: search inside partitions
Date: Fri, 2 Feb 2018 14:44:44 +0100
Message-ID: <5a746b53.6294df0a.9cba6.96a2@mx.google.com> (raw)
In-Reply-To: <CAGqkgphD6ZDxvaYRGvNmxEGUP=mB8b_JcsqMpep0_pr75EUT9A@mail.gmail.com>
References: <5a7428b2.d5171c0a.48427.4065@mx.google.com>
<CAGqkgpgyD9PDT9BTM6FFfCmnNa5HJYbuio4d6bwNHsTkQ6ZK+Q@mail.gmail.com>
<CAGqkgphD6ZDxvaYRGvNmxEGUP=mB8b_JcsqMpep0_pr75EUT9A@mail.gmail.com>
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
image001.png
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
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...;
Garanti sans virus. <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&...; www.avast.com
--
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)
| ✉︎ torsten.grust@uni-tuebingen.de
| db.inf.uni-tuebingen.de
--
| Prof. Dr. Torsten Grust
| Database Systems — Universität Tübingen (Germany)
| ✉︎ torsten.grust@uni-tuebingen.de
| db.inf.uni-tuebingen.de
---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus
Attachments:
[image/png] image001.png (56.8K, ../5a746b53.6294df0a.9cba6.96a2@mx.google.com/3-image001.png)
download | view image
view thread (4+ messages)
Message-ID: <5a746b53.6294df0a.9cba6.96a2@mx.google.com>
Permalink: ../5a746b53.6294df0a.9cba6.96a2@mx.google.com/
Also on: postgresql.org/message-id/5a746b53.6294df0a.9cba6.96a2@mx.google.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: o.lepretre@gmail.com, torsten.grust@gmail.com, pgsql-sql@lists.postgresql.org
Subject: RE: search inside partitions
In-Reply-To: <5a746b53.6294df0a.9cba6.96a2@mx.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox