From: Michael Fuhr <mike@fuhr.org>
To: Keith Worthington <keithw@narrowpathinc.com>
Cc: PostgreSQL Novice <pgsql-novice@postgresql.org>
Subject: Re: FUNCTION, TRIGGER and best practices
Date: Thu, 16 Dec 2004 23:52:09 -0700
Message-ID: <20041217065209.GA32514@winnie.fuhr.org> (raw)
In-Reply-To: <20041217040520.M87972@narrowpathinc.com>
References: <20041217040520.M87972@narrowpathinc.com>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, keithw@narrowpathinc.com, pgsql-novice@postgresql.org
Subject: Re: FUNCTION, TRIGGER and best practices
In-Reply-To: <20041217065209.GA32514@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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