I am new with SQL and I trying to make a recursive query to work on PostreSQL without any luck.

I have a table tb_invoice with these columns:

And I want to make a recursive CTE that gets the non-payed invoices bigger than 28.000eur on a single row for each cust_no. And order the result for number of invoices. It should look like something like this (in the column nombre_cliente should appear the cust_no).

But I cannot finish the code. I have tried multiple ways, but it always returns rows with repeated values or just one invoice per row.

This is some of the code I have tried. I am aware my mistake is with the conditions I give with the UNION clause, but I am unable to find a solution...

    WITH RECURSIVE lista_facturas AS (
    SELECT
        cust_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS max_numero_fila,
        CAST (invoice_no AS TEXT) AS resultado
    FROM 
        factura_cliente
-- I have tried to add multiple WHERE clauses, but they all fail...
    UNION ALL
    SELECT
        f.cust_no,
        ROW_NUMBER() OVER (PARTITION BY f.cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY f.cust_no) AS max_numero_fila,
        CAST (l.resultado || ',' || f.invoice_no AS TEXT) AS resultado
    FROM
        factura_cliente f INNER JOIN lista_facturas l
            ON (l.cust_no = f.cust_no
               AND f.numero_fila = l.numero_fila   1
               AND f.numero_fila <= l.max_numero_fila)

    -- Here I also tried 'l.invoice_no <> f.invoice_no' and other combinations
), factura_cliente AS(
    SELECT
        cust_no,
        invoice_no,
        ROW_NUMBER() OVER (PARTITION BY cust_no) AS numero_fila,
        COUNT(*) OVER (PARTITION BY cust_no) AS max_numero_fila
    FROM erp.tb_invoice i
    WHERE payed = 'N'
        AND tot_amount > 28000
)
SELECT 
    cust_no,
    resultado
FROM
    lista_facturas
;

CodePudding user response：

Use STRING_AGG function to create coma separated invoice list.

SELECT 
  cust_no, 
  STRING_AGG(invoice_no,',' ORDER BY invoice_no) invoice_list  
FROM tb_invoice
WHERE payed = 'N'
      AND tot_amount > 28000
GROUP BY cust_no;