Can SQL view have infinite number of rows? (Repeating schedule, each row a day?)

Question

Can I have a view with an infinite number of rows? I don't want to select all the rows at once, but is it possible to have a view that represents a repeating weekly schedule, with rows for any date?

I have a database with information about businesses, their hours on different days of the week. Their names:

# SELECT company_name FROM company;
     company_name
--------------------
 Acme, Inc.
 Amalgamated
...
(47 rows)

Their weekly schedules:

# SELECT days, open_time, close_time
  FROM   hours JOIN company USING(company_id)
  WHERE  company_name='Acme, Inc.';
   days  | open_time | close_time
---------+-----------+-----------
 1111100 | 08:30:00  | 17:00:00
 0000010 | 09:00:00  | 12:30:00

Another table, not shown, has holidays they're closed.

So I can trivially create a user-defined function in the form of a stored procedure that takes a particular date as an argument and returns the business hours of each company:

SELECT company_name,open_time,close_time FROM schedule_for(current_date);

But I want to do it as a table query, in order that any SQL-compatible host-language library will have no problem interfacing with it, like this:

SELECT company_name, open_time, close_time
FROM   schedule_view
WHERE  business_date=current_date;

Relational database theory tells me that tables (relations) are functions in the sense of being a unique mapping from each primary key to a row (tuple). Obviously if the WHERE clause on the above query were omitted it would result in a table (view) having an infinite number of rows, which would be a practical issue. But I'm willing to agree never to query such a view without a WHERE clause that restricts the number of rows.

How can such a view be created (in PostgreSQL)? Or is a view even the way to do what I want?

Update

Here are some more details about my tables. The days of the week are saved as bits, and I select the appropriate row using a bit mask that has a single bit shifted once for each day of the requested week. To wit:

The company table:

# \d company
               Table "company"
     Column     |          Type          | Modifiers 
----------------+------------------------+-----------
 company_id     | smallint               | not null
 company_name   | character varying(128) | not null
 timezone       | timezone               | not null

The hours table:

# \d hours
                  Table "hours"
   Column   |          Type          | Modifiers 
------------+------------------------+-----------
 company_id | smallint               | not null
 days       | bit(7)                 | not null
 open_time  | time without time zone | not null
 close_time | time without time zone | not null

The holiday table:

# \d holiday 
           Table "holiday"
    Column     |   Type   | Modifiers 
---------------+----------+-----------
 company_id    | smallint | not null
 month_of_year | smallint | not null
 day_of_month  | smallint | not null

The function I currently have that does what I want (besides invocation) is defined as:

CREATE FUNCTION schedule_for(requested_date date)
RETURNS table(company_name text, open_time timestamptz, close_time timestamptz)
AS $$
WITH field AS (
  /* shift the mask as many bits as the requested day of the week */
  SELECT B'1000000' >> (to_char(requested_date,'ID')::int -1) AS day_of_week,
  to_char(requested_date, 'MM')::int AS month_of_year,
  to_char(requested_date, 'DD')::int AS day_of_month
)
  SELECT company_name,
         (requested_date+open_time) AT TIME ZONE timezone AS open_time,
         (requested_date+close_time) AT TIME ZONE timezone AS close_time
  FROM hours INNER JOIN company USING (company_id)
       CROSS JOIN field
       CROSS JOIN holiday
         /* if the bit-mask anded with the DOW is the DOW */
  WHERE (hours.days & field.day_of_week) = field.day_of_week
  AND NOT EXISTS (SELECT 1
                  FROM holiday h
                  WHERE h.company_id = hours.company_id
                  AND   field.month_of_year = h.month_of_year
                  AND   field.day_of_month = h.day_of_month);
$$
LANGUAGE SQL;

So again, my goal is to be able to get today's schedule by doing this:

SELECT open_time, close_time FROM schedule_view
wHERE  company='Acme,Inc.' AND requested_date=CURRENT_DATE;

and also be able to get the schedule for any arbitrary date by doing this:

SELECT open_time, close_time FROM schedule_view
WHERE  company='Acme, Inc.' AND requested_date=CAST ('2013-11-01' AS date);

I'm assuming this would require creating the view here referred to as schedule_view but maybe I'm mistaken about that. In any event I want to keep any messy SQL code hidden from usage at the command-line-interface and client-language database libraries, as it currently is in the user-defined function I have.

In other words, I just want to invoke the function I already have by passing the argument in a WHERE clause instead of inside parentheses.

Answer 1

You could create a view with infinite rows by using a recursive CTE. But even that needs a starting point and a terminating condition or it will error out.

A more practical approach with set returning functions (SRF):

WITH x AS (SELECT '2013-10-09'::date AS day) -- supply your date
SELECT company_id, x.day + open_time  AS open_ts
                 , x.day + close_time AS close_ts
FROM   (
   SELECT *, unnest(arr)::bool AS open, generate_subscripts(arr, 1) AS dow
   FROM   (SELECT *, string_to_array(days::text, NULL) AS arr FROM hours) sub
   ) sub2
CROSS  JOIN x
WHERE  open
AND    dow = EXTRACT(ISODOW FROM x.day);
-- AND NOT EXISTS (SELECT 1 FROM holiday WHERE holiday = x.day)

-> SQLfiddle demo. (with constant day)

• Expanding SRFs side-by-side is generally frowned upon (and for good reason, it's not in the SQL standard and show surprising behavior if the number of elements is not the same). The new feature WITH ORDINALITY in the upcoming Postgres 9.4 will allow cleaner syntax. Consider this related answer on dba.SE or similarly:
  PostgreSQL unnest() with element number

• I am assuming bit(7) as most effective data type for days. To work with it, I am converting it to an array in the first subquery sub.

• Note the difference between ISODOW and DOW as field pattern for EXTRACT().

Updated question

Your function looks good, except for this line:

CROSS JOIN holiday

Otherwise, if I take the bit-shifting route, I end up with a similar query:

WITH x AS (SELECT '2013-10-09'::date AS day) -- supply your date
    ,y AS (SELECT day, B'1000000' >> (EXTRACT(ISODOW FROM day)::int - 1) AS dow
           FROM x)
SELECT c.company_name, y.day + open_time  AT TIME ZONE c.timezone AS open_ts
                     , y.day + close_time AT TIME ZONE c.timezone AS close_ts
FROM   hours   h
JOIN   company c USING (company_id)
CROSS  JOIN    y
WHERE  h.days & y.dow = y.dow;
AND    NOT EXISTS  ...

• EXTRACT(ISODOW FROM requested_date)::int is just a faster equivalent of to_char(requested_date,'ID')::int

"Pass" day in WHERE clause?

To make that work you would have to generate a huge temporary table covering all possible days before selecting rows for the day in the WHERE clause. Possible (I would employ generate_series()), but very expensive.

My answer to your first draft is a smaller version of this: I expand all rows only for a pattern week before selecting the day matching the date in the WHERE clause. The tricky part is to display timestamps built from the input in the WHERE clause. Not possible. You are back to the huge table covering all days. Unless you have only few companies and a decently small date range, I would not go there.

Answer 2

This is built off the previous answers.

The sample data:

CREATE temp TABLE company (company_id int, company text);
INSERT INTO company VALUES
  (1, 'Acme, Inc.')
 ,(2, 'Amalgamated');

CREATE temp TABLE hours(company_id int, days bit(7), open_time time, close_time time);
INSERT INTO hours VALUES
  (1, '1111100', '08:30:00', '17:00:00')
 ,(2, '0000010', '09:00:00', '12:30:00');

create temp table holidays(company_id int, month_of_year int, day_of_month int);
insert into holidays values
  (1, 1, 1),
  (2, 1, 1),
  (2, 1, 12) -- this was a saturday in 2013
;

First, just matching a date's day of week against the hours table's day of week, using the logic you provided:

select *
from company a
       left join hours b
         on a.company_id = b.company_id
       left join holidays c
         on b.company_id = c.company_id
where (b.days & (B'1000000' >> (to_char(current_date,'ID')::int -1)))
        = (B'1000000' >> (to_char(current_date,'ID')::int -1))
;

Postgres lets you create custom operators to simplify expressions like in that where clause, so you might want an operator that matches the day of week between a bit string and a date. First the function that performs the test:

CREATE FUNCTION match_day_of_week(bit, date)
    RETURNS boolean
    AS $$
    select ($1 & (B'1000000' >> (to_char($2,'ID')::int -1))) = (B'1000000' >> (to_char($2,'ID')::int -1))
    $$
    LANGUAGE sql IMMUTABLE STRICT;

You could stop there make in your where clause look something like "where match_day_of_week(days, some-date)". The custom operator makes this look a little prettier:

CREATE OPERATOR == (
    leftarg = bit,
    rightarg = date,
    procedure = match_day_of_week
);

Now you've got syntax sugar to simplify that predicate. Here I've also added in the next test (that the month_of_year and day_of_month of a holiday don't correspond with the supplied date):

select *
from company a
       left join hours b
         on a.company_id = b.company_id
       left join holidays c
         on b.company_id = c.company_id
where b.days == current_date
  and extract(month from current_date) != month_of_year
  and extract(day from current_date) != day_of_month
;

For simplicity I start by adding an extra type (another awesome postgres feature) to encapsulate the month and day of the holiday.

create type month_day as (month_of_year int, day_of_month int);

Now repeat the process above to make another custom operator.

CREATE FUNCTION match_day_of_month(month_day, date)
    RETURNS boolean
    AS $$
    select extract(month from $2) = $1.month_of_year
             and extract(day from $2) = $1.day_of_month
    $$
    LANGUAGE sql IMMUTABLE STRICT;

CREATE OPERATOR == (
    leftarg = month_day,
    rightarg = date,
    procedure = match_day_of_month
);

Finally, the original query is reduced to this:

select *
from company a
       left join hours b
         on a.company_id = b.company_id
       left join holidays c
         on b.company_id = c.company_id
where b.days == current_date
  and not ((c.month_of_year, c.day_of_month)::month_day == current_date)
;

Reducing that down to a view looks like this:

create view x
as
select b.days,
       (c.month_of_year, c.day_of_month)::month_day as holiday,
       a.company_id,
       b.open_time,
       b.close_time
from company a
       left join hours b
         on a.company_id = b.company_id
       left join holidays c
         on b.company_id = c.company_id
;

And you could use that like this:

select company_id, open_time, close_time
from x
where days == current_date
  and not (holiday == current_date)
;

Edit: You'll need to work on this logic a bit, by the way - this was more about showing the idea of how to do it with custom operators. For starters, if a company has multiple holidays defined you'll likely get multiple results back for that company.

Answer 3

I posted a similar response on PostgreSQL mailing list. Basically, avoiding the use of a function-invocation API in this situation is likely a foolish decision. The function call is the best API for this use-case. If you have a concrete scenario that you need to support where a function will not work then please provide that and maybe that scenario can be solved without having to compromise the PostgreSQL API. All your comments so far are about planning for an unknown future that very well may never come to be.