Is there a way to use the result of a SELECT as a column identifier in a query? (PostgreSQL)

I need to do a query like this

select * from calendar where (select to_char(now(), 'day')) = true;

but this is invalid and fails with ERROR: failed to find conversion function from unknown to boolean.

The query I'm trying write, when run today would boil down to

select * from calendar where thursday = true;

But tomorrow, it should be

select * from calendar where friday = true;

The table has this schema

mbta=# \d calendar
             Table "public.calendar"
   Column   |          Type          | Modifiers 
------------+------------------------+-----------
 service_id | character varying(255) | not null
 monday     | boolean                | 
 tuesday    | boolean                | 
 wednesday  | boolean                | 
 thursday   | boolean                | 
 friday     | boolean                | 
 saturday   | boolean                | 
 sunday     | boolean                | 
 start_date | integer                | 
 end_date   | integer             开发者_如何学JAVA   | 

How can I write this query correctly?

That's an ugly schema... A bunch of alternatives:

1. Replace 'monday, tuesday... ' fields by a single integer field, to be interpreted as a bit mask - or use the bit-string data type

2. Replace them by a single field that contatins an array of integers (days of week).

3. Denormalize to an extra table, with a single day_of_week field and a FK to your calendar table.

Yes, there is a solution. Obviously, you can't use the result of a subselect in place of a column, but you can rearrange the relation to suit that sort of query. First, build a subselect that transposes the individual columns into a single column

SELECT calendar.*, 'monday' AS weekday, monday AS dayvalue FROM calendar
UNION ALL
SELECT calendar.*, 'tuesday' AS weekday, tuesday AS dayvalue FROM calendar
UNION ALL
SELECT calendar.*, 'wednesday' AS weekday, wednesday AS dayvalue FROM calendar
UNION ALL
SELECT calendar.*, 'thursday' AS weekday, thursday AS dayvalue FROM calendar
UNION ALL
SELECT calendar.*, 'friday' AS weekday, friday AS dayvalue FROM calendar
UNION ALL
SELECT calendar.*, 'saturday' AS weekday, saturday AS dayvalue FROM calendar
UNION ALL
SELECT calendar.*, 'sunday' AS weekday, sunday AS dayvalue FROM calendar

Then you can wrap this all up as a subselect and pick out just the rows with the right weekday:

SELECT * FROM (
    SELECT calendar.*, 'monday' AS weekday, monday AS dayvalue FROM calendar
    UNION ALL
    SELECT calendar.*, 'tuesday' AS weekday, tuesday AS dayvalue FROM calendar
    UNION ALL
    ...         -- You get the idea.
    UNION ALL
    SELECT calendar.*, 'sunday' AS weekday, sunday AS dayvalue FROM calendar
) AS ss WHERE to_char(now(), 'day') = ss.weekday AND dayvalue = true; 

select * from calendar where (to_char(now(), 'day') != 'Monday' || monday) && (to_char(now(), 'day') != 'Tuesday' || tuesday) && …

Given the new schema, I think something like this is your best bet.