tucomax's questions

Tenho uma tabela assim:

-------------------------------------------------
|  id  | description         | parent_id   |  cost
--------------------------------------------------
| 1    |  Radiology         |       NULL  | 0.00
| 2    |  Lab Tests         |       NULL  | 0.00
| 3    |  Normal Radiology  |         1   | 0.00
| 4    |  Resonance         |         1   | 100.00
| 1100 |  Cerebral Resonance|         4   | 200.00
| 1900 |  Blood Tests       |         2   | 10.00
| 2044 |  Calcium           |         2   | 50.00

---------------------------------------------------

Eu preciso gerar esse tipo de saída:

Radiology
   -->Normal Radiology
   -->Resonance
      -->Cerebral Resonance with contrast
Lab Test
    --> Blood Test
    --> Calcium

Estou trabalhando no PostgreSQL. Eu tenho tentado isso com um CTE recursivo, mas não consegui gerar o que gosto:

WITH RECURSIVE hierarchy AS (
    SELECT  id, CAST(description AS TEXT) AS parent_list
    FROM    orders
    WHERE   parent_id is null
      UNION
    SELECT  c.id,
            CAST(c2.parent_list || ' --> ' || c.description as text) as parent_list
    FROM orders c
    INNER JOIN hierarchy c2 ON c.parent_id = c2.id )

SELECT  id, parent_list
FROM     hierarchy
GROUP BY id, parent_list
ORDER BY parent_list;

Esse CTE recursivo produz a seguinte saída indesejável:

Radiology
Radiology--> Normal Radiology
Radiology--> Resonance
Radiology--> Resonance --> Cerebral Resonance with contrast
Lab Test
Lab Test --> Blood Test
Lab Test --> Calcium

Como eu posso fazer isso?

Tenho uma tabela assim para salvar o catálogo de 90 serviços:

-------------------------------------------------------------------------------------
|  id  |  service     | subservice      | description                      | cost
-------------------------------------------------------------------------------------
| 2044 |   Tests      |   Tests         | Calcium                          | 50.00
| 1385 |   Cardiology |   Cardioversion | Electric Cardioversion programmed| 200.00
| 7000 |   Cardiology |  Ecocardiography| Chest Ultrasound                 | 100.00
-------------------------------------------------------------------------------------

Preciso alterar a estrutura da tabela para ter os três níveis (serviço, subserviço e descrição) na mesma coluna com id próprio e apontando para uma nova coluna com o número do nível. Ou seja, algo assim (observe que os id's são algo que eu inventei):

-------------------------------------------------------------------------------------
|  id  | description  | parent_id   |  cost
-------------------------------------------------------------------------------------
| 1    |   Tests      |       NULL  | 0.00
| 2    |   Tests      |         1   | 0.00
| 2044 |   Calcium    |         2   | 50.00
-------------------------------------------------------------------------------------

Estou trabalhando no Postgresql 12. Criei a nova coluna parent_id e estava tentando fazer esta sequência:

CREATE SEQUENCE seq_parent_id INCREMENT BY 1 START WITH 1 NO CYCLE;

ALTER TABLE catalog
ALTER COLUMN parent_id SET DEFAULT nextval('seq_parent_id')

Alguém pode por favor dar uma idéia aproximada de como transformar a estrutura da tabela?

Estou tentando fazer uma função com dois nos parâmetros ano e código de uma tabela para retornar a contagem do código e ano fornecido. A tabela é algo assim:

CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    status CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Consegui retornar a contagem com a função abaixo mas quando dou valores para ano e código em que não existe contagem ele retorna NULL mas deve ser retornado zero, como posso fazer isso?:

CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$
    
DECLARE
    total INT DEFAULT 0;
    
BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

Os dados são algo como:

INSERT INTO orders 
(id, code, service_id, status, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);