first show the sql,
select (array['Sun' ,'Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat'])
[extract(dow from now())+1];
dow means The day of week as Sunday (0) to Saturday (6),
so you just need now the number of day of week, then you can easily convert it to the name as you wish.
the PostgreSQL array simplifies the job, otherwise we need case when or store the day name of week in another table, then inner join it.