What's it all about, Alfie?

There are many times when you would like a sub-select query to be constrained on one of the values of the main query, but when you attempt to do that you get an error message about not being able to use the variable in this context. For example, this query:

  SELECT c.id, c.company_name, pb.available, pb.current,
         date_trunc('second', pb.as_of) AS as_of, pbs.*
    FROM companies c, plaid_tokens pt, plaid_balances pb,
         (SELECT SUM(available) AS all_available,
                 SUM(CURRENT) AS all_current
            FROM plaid_balances WHERE company_id=c.id) pbs
   WHERE pt.id = (c.additional_info->>'primaryPlaidAccount')::uuid
     AND pt.account_id = pb.account_id

where the goal is to have a sub-select gather the sum of the individual columns being pulled in the main query. It's a nice thing to have, but the inability to have c.id used in the sub-select really makes it difficult.

Postgres has a nice feature in LATERAL, where is allows the sub-select to reference these fields by changing the order of evaluation of the sub-select and doesn't penalize the performance too much.

  SELECT c.id, c.company_name, pb.available, pb.current,
         date_trunc('second', pb.as_of) AS as_of, pbs.*
    FROM companies c, plaid_tokens pt, plaid_balances pb,
         lateral (SELECT SUM(available) AS all_available,
                         SUM(CURRENT) AS all_current
                    FROM plaid_balances WHERE company_id=c.id) pbs
   WHERE pt.id = (c.additional_info->>'primaryPlaidAccount')::uuid
     AND pt.account_id = pb.account_id

This is still quick, and it saves the machinations of having to calculate the sums in a temp table, or write a function to do this... it's just a nice little trick that they put in the language. Very considerate. 🙂