Thread: Re: Rules/Trigges Trade-offs
Ian, > Anyway, I have a similar requirement, to intercept insert/update/delete and redirect the data if a condition is met. Right now I am trying an INSTEAD rule that puts the condtion in the WHERE of the rule definition. It seems to work OK, but if the condition has exeptions where I would like to throw an error, I can't. And since it is an INSTEAD, I can't have a trigger on the table fire to find the exception cases. Urgh. I'd suggest a "DO INSTEAD SELECT some_function(NEW)" where some_function is a function that tests for errors, throws and exception if necessary, otherwise inserts. It's tricky, but probably the best way to get trigger + rule functionality at once. -- -Josh BerkusAglio Database SolutionsSan Francisco
Ian, You're welcome. Replying to the list for the edification of other users. -Josh > I just figgered it out. I declared the function as > > create function some_func(test) returns int ... > > where test is the name of a table. The values are passed as a tcl array. I will see if RECORD works too. I wonder if that would eliminate the problem I ran into where I had dropped and recreated the table. The function barfed since the OID for the user defined type "test" did not exist. > > This will work slick. Thanks! > > >>> Josh Berkus <josh@agliodbs.com> 12/09/02 12:29PM >>> > Ian, > > > That makes sense! Is that psuedo code, or is there a way to send a function > the entire NEW array without specifying each column name and datatype > specifically. > > > > I am using pltcl, if that matters.. > > Not sure about pltcl. I'd say, try it, declare the function as: > > CREATE FUNCTION some_func (RECORD) and see how things go. > > -- > -Josh Berkus > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > > -- -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Ian, > Thanks! I would have, but my messages bounce from SQL, even though I am subscribed ( I get the messages, for crying out loud!) send an e-mail to pgsql-sql-owner@postgresql.org. > > PS RECORD doesn't work but I think that is because I am on 7.2.1.... OK. I may have tested that on 7.4 devel by accident. -- -Josh BerkusAglio Database SolutionsSan Francisco