On Thu, Oct 10, 2013 at 12:16 PM, Adam Mackler <postgres@mackler.org> wrote:
> Hi:
>
> I recently posted a question on stackoverflow, but I suspect it may
> require specific PostgreSQL knowledge, so I'm cross-posting a
> reference to it here.
>
>
http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day
>
> The gist is that I have information representing companies' daily
> schedules in some tables, and I want to be able to do a SELECT to get
> the business hours for any given day, arbitrarily far into the future
> (and past, but I can live with a limit in that direction). I want to
> encapsulate any messy SQL in a single location where it can be hidden
> from client usage, presumably in a view.
>
> I currently have a user-defined function that returns the results I
> want, but the problem is in the invocation: Some host-language client
> libraries aren't so graceful with user-defined functions, especially
> when they return multiple rows of multiple columns. I would like to
> be able to do a simple table-style SELECT query, presumably on a view.
> But if I can put any date into the WHERE clause, then that means the
> view would represent a table with an infinite number of rows, would it
> not?
>
> The posting on SO clarifies the specifics of what I'm trying to. It
> seems like there ought to be a way, but I haven't figured it out.
The trick for things like this is to cross join generate_series to the
results so that each row of the series is paramaterized through to the
rest of the data. Postgres is smart enough to optimize that so only
the data reflecting the series element is fetched although you have to
pay for the entire generate_series call (which is normally pretty
cheap).
merlin