Thread: Shortcut for defining triggers
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. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
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 LANGUAGE PLPGSQL (/* paramswould 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. 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? Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
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 > 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). As for the function name, it seems you'd want the trigger name in the function name somewhere. > 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? 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). -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
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!
Ühel kenal päeval (pühapäev, 23. jaanuar 2005, 15:49-0600), kirjutas Jim C. Nasby: > 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. The same is true for the need to define RETURN TYPE of a function separately from the function. So: How difficult would it be to provide a means to define a function and its return type in one statement? -- Hannu Krosing <hannu@tm.ee>
On Mon, Jan 24, 2005 at 10:50:13AM +0200, Hannu Krosing wrote: > ?hel kenal p?eval (p?hap?ev, 23. jaanuar 2005, 15:49-0600), kirjutas Jim > C. Nasby: > > 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. > > The same is true for the need to define RETURN TYPE of a function > separately from the function. > > So: How difficult would it be to provide a means to define a function > and its return type in one statement? I'm sorry, I must be missing something... if you're defining a trigger without seperately defining a function for it, why do you need to worry about the return type of anything? -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Mon, Jan 24, 2005 at 07:49:54PM -0800, David Fetter wrote: > > > 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. Makes sense. > > 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? How does SERIAL deal with this? It's the same issue; you're creating a sequence with a pre-defined name based on the table and field name. > 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. :) Agreed; being able to define anonymous plsql blocks is something I definately miss in PostgreSQL. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Tue, Jan 25, 2005 at 03:49:45PM -0600, Jim C. Nasby wrote: > On Mon, Jan 24, 2005 at 10:50:13AM +0200, Hannu Krosing wrote: > > ?hel kenal p?eval (p?hap?ev, 23. jaanuar 2005, 15:49-0600), kirjutas Jim > > C. Nasby: > > > 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. > > > > The same is true for the need to define RETURN TYPE of a function > > separately from the function. > > > > So: How difficult would it be to provide a means to define a > > function and its return type in one statement? > > I'm sorry, I must be missing something... if you're defining a > trigger without seperately defining a function for it, why do you > need to worry about the return type of anything? I think what Hannu was talking about is the idea of functions that return a RECORD or SETOF RECORD except that the types of all the columns are fixed. Nothing much to do w/triggers. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!