Recursive query in postgreSQL?

Question

I've looked on some recursive queries here, but haven't found my problem. In addition to that I'm new to postgreSQL.

I have the following table with example data:

number  numberto    ab  st  kz  pv
112     200                 
140     210                 
200     210                 
210     220         f2  140 H   2011
220                 f2  140 H   2011

The query will be a bit more complicated, but this is the part I can't solve. numberto refers to another number and then maybe again. I can be that this chain continues about four or five times.

Some day the other four coloumns are then NOT NULL and filled with letters and/or numbers. If ab has content, then the other three will have it too. If the last coloumns are filled, numberto can refer further, but will remain the same, so it's able to stop there.

My problem: I need to make a recursive query of one number, that then searches numberto as many times as needed until e.g. ab is then filled and get an output of them.

How can I achieve this?

SELECT version();

PostgreSQL 9.2.6 on x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.3.4 [gcc-4_3-branch revision 152973], 64-bit

Please show a useful sample of the data, at least enough to show one complete case (input and expected output). Also give your PostgreSQL version please. — Craig Ringer, Feb 10 '14 at 12:43
I tried to add sample data and add the version(); output. Please tell me if I can improve this answer. :) — Trollwut, Feb 10 '14 at 13:35
OK, so you want to walk up the numberto -> number chain until you find non-null data columns. Correct? — Craig Ringer, Feb 10 '14 at 23:09

score 3 · Accepted Answer · answered Feb 11 '14 at 11:31

3

This is a pretty simple recursive query, you just stop when you see a non-null row in the recursive term.

Given the following sample table:

WITH RECURSIVE chain AS
(
  -- Start with the row with number=140
  SELECT number, numberto, ab, st, kz, pv
  FROM numbers n
  WHERE n.number = 140
  UNION ALL
  -- and iteratively fetch the 'numberto' id'd row
  SELECT n.number, n.numberto, n.ab, n.st, n.kz, n.pv
  FROM chain c
  INNER JOIN numbers n ON (c.numberto = n.number)
  -- unless we've already found a non-null col in our last iteration
  WHERE c.ab IS NULL
)
-- Find any non-null result.
SELECT *
FROM chain c
WHERE c.ab IS NOT NULL;

should do the trick, where n.number = 140 is your start condition.

Iteration stops if you find a non-null col, or if you have a NULL numberto or non-matching numberto (as the inner join will add no rows).

answered Feb 11 '14 at 11:31

Craig Ringer

307,061
76
688
778

I tried this now a few hours... are you sure that you can refer in the second SELECT to "n."? Because my SQL program only accepts "c." there as a reference... – Trollwut Feb 17 '14 at 14:36
@Trollwut Absolutely certain; follow the SQLFiddle link that demonstrates that it runs as written. Repeating it here: http://sqlfiddle.com/#!15/41a9e/3 – Craig Ringer Feb 17 '14 at 14:42
Yessir, also with my postgreSQL version it works. But I've got a problem, that one of the rows are actually LEFT JOINed... then I don't get it to work. Despite of that, your answer is right - I will work on that tomorrow. – Trollwut Feb 17 '14 at 16:24
Ok, your answer totally fits to my question, so it is right. I'll open another question for the JOIN issue. – Trollwut Feb 18 '14 at 13:58

Recursive query in postgreSQL?

1 Answers1