Re: View challenge

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sat, 12 Aug 2006 14:14:00 GMT
Message-ID: <ImlDg.48030$vl5.20537@tornado.ohiordc.rr.com>

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. Received on Sat Aug 12 2006 - 09:14:00 CDT