Sometimes you want to use a database (e.g. postgres) to hold personal data that you would like to be able to query quickly and easily.
A nice way to do this and to keep it very simple is to keep a shell function in your .bashrc/.zshrc which you can then query on the terminal.
By way of example, let’s assume that you need help remembering the birthdays of your family and friends. You could just put a recurring event into your digital calendar, but you want to use a database, which is something I can entirely understand.
select person, birthday.birthday from birthday;| person | birthday |
|---|---|
| Jill | 1988-04-06 |
| Frodo | 1985-09-28 |
| Bob | 1989-09-11 |
| Bill | 1956-06-01 |
You want output that looks like this, and tells you which birthdays are coming up:
| person | birthday | days |
|---|---|---|
| Jill | Saturday 06 April | 58 |
| Bill | Saturday 01 June | 114 |
| Bob | Wednesday 11 September | 216 |
| Frodo | Saturday 28 September | 233 |
With this function in you .zshrc or similar, you can just run f-birthdays and have that output in your shell whenever you want it:
f-birthdays () {
cat <<EOF | psql -U postgres -h dbhostname personal
with cte_a as (select person,
make_date(
extract(year from current_date)::int,
extract(month from birthday)::int,
extract(day from birthday)::int) as bday
from birthday
where birthday is not null),
cte_b as (select *,
bday - current_date as day_delta
from cte_a),
cte_c as (select person,
to_char(bday, 'Day DD Month') as birthday,
case
when day_delta < 0
then day_delta + 365
else day_delta end as days
from cte_b)
select *
from cte_c
order by days;
EOF
}
(Note that this assumes a table called birthday in a database called personal on the hostname dbhostname, and we query with the user postgres)
To summarise:
