I don't need to insert into the 'view' anyway, and the stored procedures aren't for updating either. i already have a view that abstracts the lookup table between 'stuff' and 'accounts' (going by the tables i used in my example, when i asked about the efficiency of select statements keyed by arrays in the fashion 'WHERE ([account_id] = ANY(accounts_array)) and was advised by mr. vanasco to try a lookup table, which is working quite a bit better).
the query i will be making on the lookup table will only take three or four forms, and the inserts into the two tables it mitigates will take exactly one form. the stored procedures are just a way to parameterize the select queries, kind of like a dbh prepared statement thats always waiting for me prepared on the server. so then i can do... all_stuff_by_account_id([id]), or stuff_by_account_id_since([id],TIMESTAMP) or stuff_by_account_name_between([account_name],TIMESTAMP,TIMESTAMP), instead of recalling which where statement i mean to use in which context and specifying it each time. maybe its an immature/novice way to go about things, i don't know, like i mentioned before i just kind of got handed this job responsibility and i'm trying my best to roll with it. as much as i like Rose::DB and as much as i looooooooove perl (<--------------- THIS MUCH---------------->), i think its better to code the business logic on the server-side as much as possible, because the next developer might not want to use my perl packages and Rose::DB derived classes, or ORM at all, or perl at all, or whatever. but these procedures ensure him/her one correct way to access a very custom- tailored data set. anyway, its interesting to read everyone's opinions on this thread. this rule thing is neat and i had never heard of them before. thanks for the links, danial :-) On Jan 18, 2007, at 10:44 PM, Cees Hek wrote: > On 1/19/07, Danial Pearce <[EMAIL PROTECTED]> wrote: >> On 1/19/07, Jonathan Vanasco <[EMAIL PROTECTED]> wrote: >>> VIEWs are great, because they work out essentially as tables -- so >>> you can create ROSE DB objects for selecting (though not inserting) >> >> Sure you can do inserting. You can either do it with postgres >> triggers, or you can probably even do it hackily in rose overloading >> the save() as a wrapper for calling save() on all the other objects >> that your view deals with. Would be hacky and trickery, but not >> impossible. > > You can do it quite cleanly in PostgreSQL using Rules. The rule > system effectively lets you rewrite SQL statements before they are > executed. So a single INSERT into a VIEW can be rewritten into > multiple INSERT/UPDATE/DELETE statements to multiple different tables. > > Here is the documentation for the Rule System: > > http://www.postgresql.org/docs/8.1/static/rules.html > > And here is the specific info on how to create rules for updating > views: > > http://www.postgresql.org/docs/8.1/static/rules-update.html > > Note that this is available as far back as version 7.2 so this is not > new stuff and should be quite stable and well tested. > > Cheers, > > Cees > > ---------------------------------------------------------------------- > --- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to > share your > opinions on IT & business topics through brief surveys - and earn cash > http://www.techsay.com/default.php? > page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > Rose-db-object mailing list > Rose-db-object@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/rose-db-object ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
