Supabase is an open source Firebase alternative. Start your project with a Postgres database, Authentication, instant APIs, Edge Functions, Realtime subscriptions, and Storage.

Supabase

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

Awesome article! May i ask why using the ``ORDER BY`` directive? Isn't it more expensive in computing power? <@103875502963453952>

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

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

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

```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:

```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.

```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:

```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.

So the final output we get is the ``SELECT`` and not the ``UPDATE`` query?

I just updated my comment, so <@218505052015296512> you may want to check it out

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 <@103875502963453952> 🙂