Re: View challenge

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sat, 12 Aug 2006 14:29:58 GMT
Message-ID: <GBlDg.39785$pu3.532017@ursa-nb00s0.nbnet.nb.ca>

J M Davitt wrote:

> frebe73_at_gmail.com wrote:
>

>>> Well, that's not a view.  See those tuples with values
>>> like 2006-08-14 and 2006-08-15?  Those values don't
>>> appear in your tuples; you need something to generate
>>> those.
>>
>>
>>
>> The values a view returns doesn't need to appear in the base tables.
>> They may be calculated. The formula would be something like this:
>> starttime + i*recur_interval
>>
>>
>>> Views can present extant data in "different" ways -
>>> sometimes the difference involves applying arithmetic
>>> functions or aggregators to data and supplying values
>>> that are not directly represented in the data.  But
>>> you seem to be asking for something different here.
>>
>>
>>
>> No, it is a simple arithmetic expression.
>>
>>
>>> I've done lots of stuff like this - in Oracle, the
>>> mechanism that looks like a table but behaves like a
>>> function is called a table function and in postgres
>>> they're just called functions.
>>
>>
>>
>> Obviously it would be no problem solving it, if the DBMS supports views
>> that are backed up by a stored procedure.
>>
>>
>>> My point is, your problem seems to require that you
>>> make some data.  The difficulty is that databases
>>> store data and can present it in different forms.
>>> If you require something other than what the database
>>> provides, you'll have to write it, and it isn't a view.
>>
>>
>>
>> A RDBMS is capable of rather complex calculations.
>>
>> /Fredrick

>
>
> I'm not making myself clear; sorry.
>
> You're asking for more rows - not just different
> representations of existing data in existing rows.
>
> The best row-maker we've got is the cartesian
> product - but that won't "fill-in" any gaps: the
> only values you see "output" are representations
> of the "input."
>
> If you change the requirements so that that the
> interval [valid_from, valid_to] is constant - or,
> at least, limited to some reasonable value - and
> can presume that intervals for individuals don't
> cover each other, you can cobble together a bit
> of cheese that UNIONs a bunch of SELECT-crafted
> rows. Like the way Celko would do it.

Or he could use a closure to calculate the necessary series of integers or dates. Received on Sat Aug 12 2006 - 09:29:58 CDT