In-Reply-To: <276465.18598.qm@web31801.mail.mud.yahoo.com>
References: <276465.18598.qm@web31801.mail.mud.yahoo.com>
Mime-Version: 1.0 (Apple Message framework v752.3)
Content-Type: text/plain; charset=ISO-8859-1; delsp=yes; format=flowed
Message-Id: <75614809-F4CB-4D8B-86E6-2F600A43CF69@myemma.com>
Cc: SQL Postgresql List <pgsql-sql@postgresql.org>
Content-Transfer-Encoding: quoted-printable
From: Erik Jones <erik@myemma.com>
Subject: Re: Advice for generalizing trigger functions
Date: Thu, 27 Dec 2007 12:22:33 -0600
To: Richard Broersma Jr <rabroersma@yahoo.com>

On Dec 27, 2007, at 12:03 PM, Richard Broersma Jr wrote:

> --- On Thu, 12/27/07, Erik Jones <erik@myemma.com> wrote:
>
>> TG_TABLE_NAME will have the name of the table the trigger
>> was fired  on.  With that and using EXECUTE for your INSERT
>> statements, you'll  probably be set.
>
> True the table name is the easy part, but how do I determine the =20
> Primary Key components of the table that will also be needed in the =20=

> SQL string that will be executed?

That depends on how generalized you want this trigger function to =20
be.  If you have a set number of tables you can branch on the table =20
name to determine the id column.   That's brittle and would require =20
updating the the function every time you want to use if for a new =20
table type but if there's a limited number of tables it may work for =20
you.  Another way to go would be to the table name to join across =20
pg_class, pg_attribute, and pg_constraint.  Take a look at the table =20
layouts for those in the manual and it should be pretty clear how to =20
do that.

Erik Jones

Software Developer | Emma=AE
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com