Re: Shortcut for defining triggers - Mailing list pgsql-hackers
From | David Fetter |
---|---|
Subject | Re: Shortcut for defining triggers |
Date | |
Msg-id | 20050125034954.GL17204@fetter.org Whole thread Raw |
In response to | Re: Shortcut for defining triggers ("Jim C. Nasby" <decibel@decibel.org>) |
Responses |
Re: Shortcut for defining triggers
|
List | pgsql-hackers |
On Mon, Jan 24, 2005 at 08:40:30PM -0600, Jim C. Nasby wrote: > On Mon, Jan 24, 2005 at 08:12:49AM -0800, David Fetter wrote: > > On Sun, Jan 23, 2005 at 03:49:22PM -0600, Jim C. Nasby wrote: > > > Sorry if this is old, but I couldn't find it in the archives... > > > > > > How difficult would it be to provide a means to define a trigger in > > > one statement? Something like a combination of CREATE TRIGGER and > > > CREATE FUNCTION? Being able to define them seperately is awesome for > > > generic cases where you can use one function for a bunch of > > > different tables, but it's a pain in the cases where you need a > > > unique trigger for one table. > > > > What would you want the function name to default to? What language, > > or would you want to specify that somehow? > > > > Here's a sketch of what such an API might look like: > > > > CREATE TRIGGER foo_trg > > BEFORE INSERT OR UPDATE ON foo_tab > > FOR EACH ROW EXECUTE PROCEDURE ^^^^^^^^^^^^^^^^^ Maybe this should read "EXECUTE DYNAMIC PROCEDURE" or some such in order to make things easier on the parser. > > LANGUAGE PLPGSQL (/* params would go here if any */) $$ > > /* body here */ > > $$; > > > > This would cause a foo_tab_b4_iu_func (how to address namespace > > collisions?) to be created in the appropriate language with > > appropriate params, then the foo_trg on the table. > > Yes, that's what I was thinking. If we wanted to get really clever, > theoretically the function wouldn't even need to be named, but of > course that would mean having to different sets of trigger code, > which is probably a BadIdea(tm). Yesâ¢, It Is®[1]. > As for the function name, it seems you'd want the trigger name in > the function name somewhere. No matter what you do, there has to be some kind of fallback for namespace collision. How would this work? > > Does SQL:2003 have anything to say about this? Also, what kind of > > development effort would be involved with an implementation, > > assuming SQL:2003 doesn't forbid? > > Does the SQL standard even address triggers that only call a > function? Dunno. SQL:2003 is written in what appears to be Klingon legalese, or possibly ceremonial Navajo. Maybe some of each. > PostgreSQL is the only database I've used that does this (all the > other ones just have you provide the procedural code you want run > when the trigger fires). With Oracle, anyhow, there's a default language: PL/SQL. With PostgreSQL, things are a little more flexible, which takes away the tight integration. This is both good and bad. I'd like to see some way to CALL anonymous blocks of [your favorite PL/], and this might even have something to do with what you're describing. :) Cheers, D [1] It doesn't even depend on what your definition of 'is' is. ;) -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
pgsql-hackers by date: