https://supabase.com/ logo
#reading
Title
# reading
g

grabbou

09/21/2022, 8:20 PM
I've been playing around with database functions for past couple of days and decided to write a quick article explaining some benefits and how to set it up! Hope that helps! https://twitter.com/grabbou/status/1572681507067817985?s=20&t=Lskf0-SARCyVp5se2_Z2oA
o

Olyno

09/21/2022, 8:41 PM
Awesome article! May i ask why using the ``ORDER BY`` directive? Isn't it more expensive in computing power? @grabbou
g

grabbou

09/21/2022, 8:51 PM
Do you mean
GROUP BY
? I think I didn't use
ORDER BY
anywhere throughout the code 😅
That one was just specific to my challenge - since I was using aggregate function (
sum
) to calculate total value of all records, I had to group them by something
o

Olyno

09/21/2022, 9:14 PM
Yooo i'm tired or what, looks like i'm saying shit since the last hour xD Yeah i meant ``GROUP BY``
Oh okay, so it's optional, it's just for your use-case?
'cause something i'm not sure to understand and which confuse me is how your ``GROUP BY`` is used. I'm not sure to understand why using it in the sum part instead of the global scope
g

grabbou

09/21/2022, 9:22 PM
Yeah it’s only there because of my update statement
It is needed inside, not on the global scope, because that's where we apply our aggregation
Copy code
sql
UPDATE public."Wallet" w
SET balance = sub.balance
FROM (
  SELECT SUM(delta) AS balance
  FROM public."Transaction" t
  WHERE t."walletId" = new."walletId"
  GROUP BY t."walletId"
) sub
WHERE w.id = new."walletId";
In other words: When new transaction is created, it will be accessible via
new
variable. Since transaction has
walletId
(that's my schema model), I can get the Wallet that transaction was added to by comparing IDs:
Copy code
sql
WHERE w.id = new."walletId";
Once I have that matching Wallet, next step is to update the balance. How to do it? Well, we do a sub-query. Inside a sub-query, we SELECT all transactions for a given
walletId
(new transaction + everything that happened in the past) and we perform aggregation to summarise your balance.
Copy code
sql
SELECT SUM(delta) AS balance
FROM public."Transaction" t
WHERE t."walletId" = new."walletId"
GROUP BY t."walletId"
Then, we just substitute that in the original query after
FROM
, so we can actually select these fields:
Copy code
sql
UPDATE public."Wallet" w
SET balance = sub.balance
FROM (
  SELECT SUM(delta) AS balance
  FROM public."Transaction" t
  WHERE t."walletId" = new."walletId"
  GROUP BY t."walletId"
) sub
WHERE w.id = new."walletId";
And we're set.
o

Olyno

09/21/2022, 9:28 PM
So the final output we get is the ``SELECT`` and not the ``UPDATE`` query?
g

grabbou

09/21/2022, 9:29 PM
I just updated my comment, so @Olyno you may want to check it out
Does that help? 🤔
o

Olyno

09/21/2022, 9:35 PM
Sure it does, thank you very much!
g

grabbou

09/21/2022, 9:35 PM
np! 🙏 thanks for giving it a read!
s

SimonP

09/22/2022, 8:27 PM
I am still not sure how to think about triggers and pl/sql functions... In general these are rather seen as technical debs than a go-to solution... Anyway the article is great, good job @grabbou 🙂
2 Views