Re: Shortcut for defining triggers

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(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!