Question 89453 in PostgreSQL Question posted in
The official documentation can be found here.

How do I translate below MySQL query with variable usage to PostgresSQL query – Postgresql

I’m new to using postgresql and looking for some help on how translate these vars usage in MySql to Postgres it looks like postgres doesn’t support this type of syntax.

I do not want to use recursion because max_depth is already known and ideally I want a similar query without recursion.

select
       name
from
       (
              select
                     @lidx := field(
                            coalesce(
                                   @root_id, coalesce(p10.id,p9.id,p8.id,p7.id,p6.id,p5.id,p4.id,p3.id,p2.id,p1.id)
                            ),
                            p1.id,p2.id,p3.id,p4.id,p5.id,p6.id,p7.id,p8.id,p9.id,p10.id
                     ) as leftmost,
                     concat(
                            if(@lidx >= 10, rpad(p10.name, 24, ' '), ''),
                            if(@lidx >= 9, rpad(p9.name, 24, ' '), ''),
                            if(@lidx >= 8, rpad(p8.name, 24, ' '), ''),
                            if(@lidx >= 7, rpad(p7.name, 24, ' '), ''),
                            if(@lidx >= 6, rpad(p6.name, 24, ' '), ''),
                            if(@lidx >= 5, rpad(p5.name, 24, ' '), ''),
                            if(@lidx >= 4, rpad(p4.name, 24, ' '), ''),
                            if(@lidx >= 3, rpad(p3.name, 24, ' '), ''),
                            if(@lidx >= 2, rpad(p2.name, 24, ' '), ''),
                            rpad(p1.name, 24, ' ')
                     ) as locator,
                     -- Prepend a tab to the name for every level of the tree after the root.
                     concat(repeat('\t', @lidx - 1), p1.name) as name
              from
                     (
                            -- Declare constants
                            select
                                   -- company_id. Leave null for all companies
                                   @company_id := null,
                                   -- root_id. Leave null for all departments
                                   @root_id := null
                     ) sqlVars,
                     hierarchy p1
                     -- repeatedly left join until the desired max depth (10)
                     left join hierarchy p2 on p2.id = p1.parent_id
                     left join hierarchy p3 on p3.id = p2.parent_id
                     left join hierarchy p4 on p4.id = p3.parent_id
                     left join hierarchy p5 on p5.id = p4.parent_id
                     left join hierarchy p6 on p6.id = p5.parent_id
                     left join hierarchy p7 on p7.id = p6.parent_id
                     left join hierarchy p8 on p8.id = p7.parent_id
                     left join hierarchy p9 on p9.id = p8.parent_id
                     left join hierarchy p10 on p10.id = p9.parent_id
              where
                     (      -- filter on company_id if non null
                            @company_id is null
                            or @company_id = p1.company_id
                     )
                     and (  -- filter on root_id if non null
                            @root_id is null
                            or @root_id in (p1.id,p2.id,p3.id,p4.id,p5.id,p6.id,p7.id,p8.id,p9.id,p10.id)
                     )
              -- alpha ordering
              order by 
                     locator
       ) flattened;

For checking the current SQL and usage and for running it please use the below sql fiddle

SQL Fiddle[1]: http://sqlfiddle.com/#!9/faf62e/200

Tags:

2

Answers


  1. 0

    This gets close:

    with recursive tree (id, name, level) as (
  select id, name, 0 from hierarchy where parent_id is null
  union all
  select hierarchy.id, hierarchy.name, tree.level+1
  from hierarchy join tree on hierarchy.parent_id = tree.id
)
select repeat('t', level) || name from tree order by id;

    SQLFiddle

    It doesn’t reproduce the row order exactly the same as your sqlfiddle, but this is the way to do a recursive query in PostgreSQL (or in MySQL 8.0).

    Read the manual for more information on recursive CTE syntax: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE

    Login or Signup to reply.
  2. 0

    You can use a recursive query:

    with recursive tree as (
  select name, id, 0 as level
  from hierarchy 
  where parent_id is null
  union all
  select c.name, c.id, p.level + 1
  from hierarchy c
    join tree p on p.id = c.parent_id
) 
search depth first by name set sort_order
select repeat(' ', level * 2)||name
from tree
order by sort_order;

    By using the search depth first we can keep the sub-trees together.

    Note that I used spaces to achieve the indention because tabs aren’t shown in dbFiddle.

    Online example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search