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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Adam Mackler-3 wrote
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Oct 10, 2013 at 10:42:47AM -0700, David Johnston wrote:
Adam Mackler-3 wrote
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:
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general