Recursive query in PostgreSQL. SELECT *

Question

I have recursive query to retrieve all children of a given person

WITH RECURSIVE recursetree(id, parent_id) AS (
    SELECT id, parent_id FROM tree WHERE parent_id = 0
  UNION
    SELECT t.id, t.parent_id
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT * FROM recursetree;

As you can see, I'm specifying list of columns to be retrieved. But I want to use SELECT * (I have really many columns in real table, and they can be changed in future). Is there some way to get ALL columns without defining each column individually?

user330315 · Accepted Answer · 2015-04-29 13:24:30Z

10

You don't need to specify the columns in the WITH part. If you leave that out, the column names will be determined by the first query in the UNION:

WITH RECURSIVE recursetree AS (
    SELECT * FROM tree WHERE parent_id = 0
  UNION
    SELECT t.*
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
)
SELECT * 
FROM recursetree;

answered Apr 29, 2015 at 13:24

user330315

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

4 Comments

wildplasser Over a year ago

Yours is cleaner! I don't know if the intermediate CTE object being "fatter" will affect performance, (I'm afraid it will)

user330315 Over a year ago

@wildplasser: only an explain (analyze, verbose) can tell ;)

wildplasser Over a year ago

But I am too lazy to set up a test rig. I'd have to type in all the column names! catb.org/jargon/html/R/recursion.html

wildplasser Over a year ago

I just checked. My intuition was right. 100K rows(22columns, rowsize ~650) ; mine: 439.704ms, yours: 15984.338ms

wildplasser · Accepted Answer · 2015-04-29 13:17:01Z

7

quick hack-around (rejoin with the original):

WITH RECURSIVE recursetree(id, parent_id) AS (
    SELECT id, parent_id FROM tree WHERE parent_id = 0
  UNION
    SELECT t.id, t.parent_id
    FROM tree t
    JOIN recursetree rt ON rt.id = t.parent_id
  )
SELECT *
FROM tree tr
WHERE EXISTS ( SELECT 1
    FROM recursetree x
    WHERE x.id = tr.id
    );

answered Apr 29, 2015 at 13:17

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

Recursive query in PostgreSQL. SELECT *

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related