Обсуждение: Can a view represent a schedule for all days into the future?
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. Thanks very much. -- Adam Mackler
Adam Mackler-3 wrote > http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day > > 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. Not sure how you can state "But I'm willing to agree never to query such a view without a WHERE clause that restricts the number of rows." when you cannot even guarantee which host-language client libraries you need to support. The use-case you are stating is best solved via the creation of a user-defined function. I would implement that and then, in the off chance there is some kind of client-library interface issue, solve that specific problem when it arises. Implementing a less-than-ideal solution today for a problem that may never even come up is foolish. More specifically you cannot model infinity in this situation. The best you could do is use "generate_series(...)" to construct and appropriately large domain of values which would then be filtered. If you want to provide a concrete situation that you must handle and that the function-invocation form of the API will not work please do so and maybe some advice can be provided to solve that problem. Unless and until you can do that just use the function-invocation form and be content. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Can-a-view-represent-a-schedule-for-all-days-into-the-future-tp5774069p5774076.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Oct 10, 2013 at 10:42:47AM -0700, David Johnston wrote: > Adam Mackler-3 wrote > > http://stackoverflow.com/questions/19237450/can-sql-view-have-infinite-number-of-rows-repeating-schedule-each-row-a-day > > > Not sure how you can state "But I'm willing to agree never to query such a > view without a WHERE clause that restricts the number of rows." when you > cannot even guarantee which host-language client libraries you need to > support. I'm willing to agree that if I ever query such a view without a WHERE clause that restricts the number of rows, then I won't blame anyone but myself for the consequences. If someone can answer this challenge without imposing that requirement, then I shall be all the more impressed. The library I'm using is SLICK: http://slick.typesafe.com/ As far as I can tell it can't handle user-defined functions that return multiple rows nor multiple columns, except perhaps through some low-level SQL interface that would defeat my idea of the purpose of using a database interface library. As I mention in the post on SO, my understanding of relational database theory tells me that tables/relations ARE functions, in the sense of being a mapping between a primary key and a row. So there's nothing ambiguous about issuing a SELECT query for some particular date, and if I'm only querying for one date, then there's only one day in the resulting table. It wouldn't surprise me if there were some way to create a view based on underlying tables that contain schedules for each day of the week that I could query in such a fashion for any day arbitrarily far into the future. If this is possible, then I would be interested in knowing what the creation of such a view looks like, independently of my immediate practical needs. Call me academic. -- Adam Mackler
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
Adam Mackler-3 wrote > If someone can answer this challenge > without imposing that requirement, then I shall be all the more > impressed. Fair enough; my goal wasn't to complete a challenge but to actually be practical. > The library I'm using is SLICK: > > http://slick.typesafe.com/ > > As far as I can tell it can't handle user-defined functions that > return multiple rows nor multiple columns, except perhaps through some > low-level SQL interface that would defeat my idea of the purpose of > using a database interface library. You seem to be trying to fix the wrong end of the problem then... > As I mention in the post on SO, my understanding of relational > database theory tells me that tables/relations ARE functions, in the > sense of being a mapping between a primary key and a row. So there's > nothing ambiguous about issuing a SELECT query for some particular > date, and if I'm only querying for one date, then there's only one day > in the resulting table. > > It wouldn't surprise me if there were some way to create a view based > on underlying tables that contain schedules for each day of the week > that I could query in such a fashion for any day arbitrarily far into > the future. If this is possible, then I would be interested in > knowing what the creation of such a view looks like, independently of > my immediate practical needs. Call me academic. Yes, there is probably some poorly performing and hard-to-maintain way to accomplish your goal using triggers, rules and materialized views. Relational theory only gets you so far. The actual tools implemented in PostgreSQL are what we have to work with and given the toolbox in hand, and my knowledge of it (which is reasonably complete but not perfect) a function API is, IMO, the most effective solution. If your method of coding cannot make use of that API you should decide what you want to do to handle such since this kind of API is quite common - at least in PostgreSQL which is all that I am concerned with for purposes of this discussion. I'm guessing that your ORM will allow you to handle this use-case in some manner otherwise it is not a very slick ORM. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Can-a-view-represent-a-schedule-for-all-days-into-the-future-tp5774069p5774115.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.