Created
March 20, 2020 07:34
-
-
Save dilame/dcb55112833d0ad6579a08fd573592da to your computer and use it in GitHub Desktop.
Postgresql basic accounting
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create table public.account | |
( | |
id bigint not null primary key generated always as identity, | |
created_at timestamp with time zone default now() not null, | |
display_name text, | |
picture text, | |
); | |
create table currency | |
( | |
id text not null primary key, | |
template text not null | |
); | |
create table public.accounting_entry | |
( | |
id bigint not null primary key generated always as identity, | |
created_at timestamp with time zone default now() not null, | |
account_id bigint not null | |
references account | |
on update cascade on delete cascade, | |
currency_id text not null | |
references currency | |
on update cascade on delete restrict, | |
amount numeric not null | |
constraint not_zero_amount check ( amount <> 0 ) | |
); | |
create table public.account_balance | |
( | |
account_id bigint not null | |
references account | |
on update cascade on delete cascade, | |
currency_id text not null | |
references currency | |
on update cascade on delete restrict, | |
amount numeric not null check ( amount >= 0 ), | |
primary key (account_id, currency_id) | |
); | |
create or replace function public.accounting_entry_process() returns trigger as | |
$$ | |
declare | |
ae public.accounting_entry%ROWTYPE; | |
begin | |
ae := NEW; | |
insert into public.account_balance (account_id, currency_id, amount) | |
values (ae.account_id, ae.currency_id, ae.amount) | |
on conflict (account_id, currency_id) do update set amount = EXCLUDED.amount + ae.amount; | |
return ae; | |
end; | |
$$ | |
language plpgsql; | |
create trigger process_insert | |
before insert | |
on public.accounting_entry | |
for each row | |
execute procedure public.accounting_entry_process(); | |
create view public.account_balance_view as | |
select a.id as account_id, c.id as currency_id, coalesce(ab.amount, 0) as amount | |
from public.account as a | |
cross join public.currency c | |
left join account_balance ab on a.id = ab.account_id and c.id = ab.currency_id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment