pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
FUNCTION, TRIGGER and best practices 3+ messages / 3 participants [nested] [flat]
* FUNCTION, TRIGGER and best practices @ 2004-12-17 04:05 Keith Worthington <keithw@narrowpathinc.com> 0 siblings, 2 replies; 3+ messages in thread From: Keith Worthington @ 2004-12-17 04:05 UTC (permalink / raw) To: PostgreSQL Novice <pgsql-novice@postgresql.org> Hi All, I have a function/trigger pair operating that move data from one table to another after that data is inserted into the first table. Can triggers and functions have the same name? Is this a good practice? Where should the function and trigger be stored? In the same schema as the table they are connected to? I will now be attempting to expand this function to move data from two source tables to four or more target tables. (Different source columns go to different tables.) Is it possible to do a transaction/commit/rollback inside a function that is driven by a trigger so that if any portion of the transfer fails it is all rolled back? Thanks to all on this list that have helped me get this far. :-) Kind Regards, Keith ______________________________________________ 99main Internet Services http://www.99main.com ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FUNCTION, TRIGGER and best practices @ 2004-12-17 05:43 Josh Berkus <josh@agliodbs.com> parent: Keith Worthington <keithw@narrowpathinc.com> 1 sibling, 0 replies; 3+ messages in thread From: Josh Berkus @ 2004-12-17 05:43 UTC (permalink / raw) To: pgsql-sql; KeithW@narrowpathinc.com; +Cc: PostgreSQL Novice <pgsql-novice@postgresql.org> Keith, > Can triggers and functions have the same name? Yes. > Is this a good practice? It can be. If the function does nothing but power the trigger, sure. Or you can use prefixes or suffixes to distinguish them. For example, if you had an audit trigger on the companies table, you might call the function audit_companies_tf, the trigger audit_companies_tg. > Where should the function and trigger be stored? Um, in the database? > In the same schema as the table they are connected to? I'm not sure you CAN put the trigger in a different schema from the table. It's a dependant object. > I will now be attempting to expand this function to move data from two > source tables to four or more target tables. (Different source columns go > to different tables.) Is it possible to do a transaction/commit/rollback > inside a function that is driven by a trigger so that if any portion of the > transfer fails it is all rolled back? Well, if any portion of a trigger fails, everything is rolled back; so is the insert, for a before trigger. So that might be the way to handle the double-trigger situation .... except they'll be in two different tables, yes? So that'll require an explicit transaction wrapping the two inserts. -- Josh Berkus Aglio Database Solutions San Francisco ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FUNCTION, TRIGGER and best practices @ 2004-12-17 06:52 Michael Fuhr <mike@fuhr.org> parent: Keith Worthington <keithw@narrowpathinc.com> 1 sibling, 0 replies; 3+ messages in thread From: Michael Fuhr @ 2004-12-17 06:52 UTC (permalink / raw) To: Keith Worthington <keithw@narrowpathinc.com>; +Cc: PostgreSQL Novice <pgsql-novice@postgresql.org> On Thu, Dec 16, 2004 at 11:05:20PM -0500, Keith Worthington wrote: > Can triggers and functions have the same name? The "Trigger Procedures" section of the "PL/pgSQL - SQL Procedural Language" chapter in the documentation shows a trigger having the same name as the function it calls. And you could always try it. > Is this a good practice? I'd suggest using whatever names seem the most self-documenting for a particular application. Maybe somebody else will present arguments advocating one style or another; I don't have strong opinions either way. Multiple triggers on a table will fire alphabetically by trigger name, so firing order might affect how you name the triggers. > Where should the function and trigger be stored? > In the same schema as the table they are connected to? If objects (functions, types, etc.) are dedicated to tables in a particular schema then I usually create them in that schema to keep everything together. That can be handy for doing backups with "pg_dump --schema schemaname". > I will now be attempting to expand this function to move data from two source > tables to four or more target tables. (Different source columns go to > different tables.) Is it possible to do a transaction/commit/rollback inside > a function that is driven by a trigger so that if any portion of the transfer > fails it is all rolled back? See the last paragraph of the "Structure of PL/pgSQL" section in the "PL/pgSQL - SQL Procedural Language" chapter of the documentation, as well as the "Errors and Messages" section in the same chapter. Functions can't execute COMMIT or ROLLBACK, but they can raise an exception to abort the current transaction. In PostgreSQL 8.0 functions can trap errors, effectively allowing you to have a subtransaction inside your function. See "Trapping Errors" in the "Control Structures" documentation for details. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-12-17 06:52 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2004-12-17 04:05 FUNCTION, TRIGGER and best practices Keith Worthington <keithw@narrowpathinc.com> 2004-12-17 05:43 ` Josh Berkus <josh@agliodbs.com> 2004-12-17 06:52 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox