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
– Craig Ringer, Commented Feb 10, 2014 at 12:43
I tried to add sample data and add the version(); output. Please tell me if I can improve this answer. :) — Trollwut
– Trollwut, Commented Feb 10, 2014 at 13:35
OK, so you want to walk up the numberto -> number chain until you find non-null data columns. Correct? — Craig Ringer
– Craig Ringer, Commented Feb 10, 2014 at 23:09

Craig Ringer · Accepted Answer · 2014-02-11 11:31:50Z

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, 2014 at 11:31

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Trollwut Over a year ago

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...

Craig Ringer Over a year ago

@Trollwut Absolutely certain; follow the SQLFiddle link that demonstrates that it runs as written. Repeating it here: sqlfiddle.com/#!15/41a9e/3

Trollwut Over a year ago

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 Over a year ago

Ok, your answer totally fits to my question, so it is right. I'll open another question for the JOIN issue.

Collectives™ on Stack Overflow

Recursive query in postgreSQL?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related