PostgreSQL / Destructuring assignment

Question

I have used destructuring assignment terminology for my case. Some programming languages allow us to make a multiple assignment:

JavaScript:

let a, b;
[a, b] = [10, 20];

Is It possible to make something similar in PostgreSQL to avoid using of duplicated sub queries?

We have a table categories:

|-----------------------|---------------------------|------------------|
|      category_id      |     parent_category_id    |       name       |
|-----------------------|---------------------------|------------------|
|            1          |           NULL            |      'main'      | <= parent category
|-----------------------|---------------------------|------------------|
|            2          |             1             |      'sub1'      |
|-----------------------|---------------------------|------------------|
|            3          |             1             |      'sub2'      |
|-----------------------|---------------------------|------------------|

We need to make a select a only sub category by the name, but if this name belogns to a parent category, get all subcategories under that parent.

in case of name = 'main' we'll get 2 categories (category IDs: 2, 3)

in case of name = 'sub2' only 1 record (category ID: 3)

Is there a way to optimize this query:

SELECT 
    c.category_id 
FROM
    categories c 
WHERE
    c.category_id = ANY 
        (SELECT c2.category_id FROM categories c2 WHERE c2.name = $1)
    OR
    c.parent_category_id = ANY 
        (SELECT c3.parent_category_id FROM categories c3 WHERE c3.name = $1)

user330315 · Accepted Answer · 2020-02-03 09:54:13Z

3

It seems you are looking for a recursive query:

with recursive tree as (
  select id, parent_id, name
  from categories
  where name = $1 --<< start with this category
  union all
  select child.id, child.parent_id, child.name
  from categories child 
     join tree parent on parent.id = child.parent_id
)
select *
from tree

Note that this will yield strange results if name isn't unique

answered Feb 3, 2020 at 9:54

user330315

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

Collectives™ on Stack Overflow

PostgreSQL / Destructuring assignment

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related