On Wed, Aug 13, 2008 at 1:58 AM, Baron Schwartz <[EMAIL PROTECTED]> wrote: > On Tue, Aug 12, 2008 at 7:35 PM, Brian Aker <[EMAIL PROTECTED]> wrote: >> Hi! >> >> On Aug 12, 2008, at 4:26 PM, Baron Schwartz wrote: >> >>> Even if it's not pluggable, I see no good reason that I shouldn't be >>> able to make a VARCHAR column auto-update to the current time. (Or >>> current date, or whatever). >> >> >> Do you want to be able to run an arbitrary function? > > Yes. Arbitrary SQL expressions would be nice too. > > I don't see it happening every day, but... > > create table user ( > user_id int, > game_points int, > rank int on update (select count(*) from user where game_points >= > this.game_points) > ); > > It's an absurd example, and would be better done as a trigger, but if > it's pluggable, why not let people do stuff like this? > > I'm struggling to think of a better example -- something like setting > the user active when they log in, but you'd just do that with an > UPDATE... dammit, examples ought to come out of the woodwork here... > > I see this as being parser hell. So IMO constraints and defaults > would be better off implemented separately, stored separately from the > table definition, and sort of "attached" to the table the way you > would attach a listener in an object-oriented programming language > (take your pick of design patterns there). > > The more I talk the more I'm thinking this is just triggers by a > different name. Which is what timestamp's current functionality > really is. > > Oh, I know what would be useful -- > > create table urls ( > url varchar(1000), > url_crc int on update crc32(url_varchar), > key(url_crc) > ) > > Again it's a trigger in a different guise, but maybe there's a sweet spot > here. > > And this last example sucks because all it demonstrates is the need to > index a function: > > create table urls ( > url varchar(1000), > key(crc32(url_crc)) > ) > > OK I give up. But there's something useful in here, I just can't articulate > it. > > _______________________________________________ > Mailing list: https://launchpad.net/~drizzle-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~drizzle-discuss > More help : https://help.launchpad.net/ListHelp >
Baron, the examples you mentioned could be implemented by a trigger, but with an important difference. Triggers will make the calculation permanent, and store the result in the table. What a calculated field should do is allow for calculations during SELECT, not (only) during INSERT or UPDATE. e.g.: create table accounts ( user_name varchar(100), creation_date date, is_recent BOOLEAN as ( to_days(now()) - to_days(creation_date) < 7) ); You can't do that with a trigger (although you can do it with views). Giuseppe -- The Data Charmer http://datacharmer.org/ _______________________________________________ Mailing list: https://launchpad.net/~drizzle-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
