Create a view containing multiple rows per customer I'm creating a TASKS view that should list up to 10 different "todo" tasks per customer in my system.

Example tasks could be "check customer order", "send customer order", "enter invoice in external system"... At a given time, there can be multiple active tasks for a customer.

All these tasks are performed manually and I want to consolidate them all in a view so that I can display them in a single screen, send email notifications when they are due, etc...

My current query looks like this :

SELECT 1 as task_id, customer_id, created as since, confirmed as done
FROM CUSTOMERS
WHERE cancelled IS NULL
...
UNION ALL
SELECT 12 as task_id, customer_id, invoice_paid as since, invoice_registered as done
FROM CUSTOMERS
WHERE cancelled IS NULL AND has_invoice


The query is working fine but the performance is not so great.

Even queries like SELECT * FROM TASKS where customer_id = 12 take a few seconds.

Reading the CUSTOMERS table 12 times to generate only 0 or 1 row per task_id seems wasteful.

Is there any way to improve it ? I had thought of something along these lines (but it is not valid SQL) :

SELECT * FROM (
    SELECT VALUES (1 as task_id, customer_id, created as since, confirmed as done, cancelled_date is not null as ignore)
    ,...
    , (12 as task_id, customer_id, invoice_paid as since, invoice_registered as done, cancelled_date is not null or not has_invoice as ignore)
    FROM CUSTOMERS
) a
WHERE not a.ignore;
You can use a LATERAL join to rewrite the view:

CREATE VIEW tasks AS
SELECT c.customer_id, t.*
FROM customers AS c,
     LATERAL
     (   SELECT  1::int, c.created, c.confirmed   
       UNION ALL
         SELECT  2, ---
         WHERE ---
       ...
       UNION ALL
         SELECT 12, c.invoice_paid, c.invoice_registered
         WHERE c.has_invoice
     ) AS t (task_id, since, done)
WHERE c.cancelled IS NULL ;


Adding a filtered index on (customer_id) WHERE (cancelled IS NULL) should  improve efficiency (perhaps of the existing view as well, although I'm not sure if the condition will be pushed down through the UNION) for the query you use:

SELECT * FROM tasks WHERE customer_id = 12 ;