Thread: Having a problem with my stored procedure

Having a problem with my stored procedure

From

Laura McCord

Date:

13 February 2007, 16:10:17

To make a long story short, I am archiving data from an original table
to a table I created. This is a third party web application that I am
doing this with, so I can't revise the structure/code of this
application. With this said, if the original table goes through an
insert or update action I want to replicate the information to my
archive table. I don't want to delete any articles from my archive
table so this is why I am not wanting to do anything based on a delete
action.

The only problem that I am facing is how to tell the function that I want to perform an update if an update occurred
andan insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based
onan insert or update.  

Help, I've been stumped for two days.
Thanks in advance.

This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles();



CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
 tmp_news_id CHARACTER varying(48);
 tmp_title CHARACTER varying(100);
 tmp_abstract CHARACTER varying(300);
 tmp_news_story TEXT;
 tmp_topic_id CHARACTER varying(10);
 tmp_create_date DATE;
 tmp_author CHARACTER varying(50);
 tmp_begin_date DATE;
 tmp_end_date DATE;
 tmp_priority CHARACTER(1);
 tmp_image_name CHARACTER varying(512);
 tmp_image_mime_type CHARACTER varying(50);
 tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where last_inserted(news_id);
SELECT INTO  tmp_abstract abstract from news_content where last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_create_date create_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_end_date end_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where last_inserted(news_id);
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where last_inserted(news_id);

//This is to be done if an INSERT action was done on the table

INSERT INTO su_archives(news_id, title, abstract, news_story,
topic_id, create_date, author, begin_date, end_date, priority,
image_name, image_mime_type, layout_type) VALUES
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
image_name ,tmp_image_mime_type,tmp_layout_type);

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE

RETURN NEW;
END
';

Re: Having a problem with my stored procedure

From

"William Leite Araújo"

Date:

13 February 2007, 16:20:08

2007/2/13, Laura McCord <mccordl@southwestern.edu>:

To make a long story short, I am archiving data from an original table
to a table I created. This is a third party web application that I am
doing this with, so I can't revise the structure/code of this
application. With this said, if the original table goes through an
insert or update action I want to replicate the information to my
archive table. I don't want to delete any articles from my archive
table so this is why I am not wanting to do anything based on a delete
action.

The only problem that I am facing is how to tell the function that I want to perform an update if an update occurred and an insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based on an insert or update.

Help, I've been stumped for two days.
Thanks in advance.

This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles();

CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
tmp_news_id CHARACTER varying(48);
tmp_title CHARACTER varying(100);
tmp_abstract CHARACTER varying(300);
tmp_news_story TEXT;
tmp_topic_id CHARACTER varying(10);
tmp_create_date DATE;
tmp_author CHARACTER varying(50);
tmp_begin_date DATE;
tmp_end_date DATE;
tmp_priority CHARACTER(1);
tmp_image_name CHARACTER varying(512);
tmp_image_mime_type CHARACTER varying(50);
tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where last_inserted(news_id);
SELECT INTO  tmp_abstract abstract from news_content where last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_create_date create_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_end_date end_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where last_inserted(news_id);
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where last_inserted(news_id);

IF TG_OP = 'INSERT' THEN

//This is to be done if an INSERT action was done on the table

INSERT INTO su_archives(news_id, title, abstract, news_story,
topic_id, create_date, author, begin_date, end_date, priority,
image_name, image_mime_type, layout_type) VALUES (tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
image_name ,tmp_image_mime_type,tmp_layout_type);

ELSEIF TG_OP = 'UPDATE' THEN

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE

END IF;

RETURN NEW;
END
';

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

--
William Leite Araújo
Analista de Banco de Dados - QualiConsult

Re: Having a problem with my stored procedure

From

"Ted Byers"

Date:

13 February 2007, 17:14:58

Would it not be simpler to just create two trigger functions, one that acts on insert operations and a second that acts on update operations? A 30 second glance at the Postgresql documentation showed me that it is possible to have more than one row level trigger for a given table, which implies the simpler options is possible. This would make for a much simpler design and avoid a conditional block that would then be unnecessary. This extra cost is, of course, trivial if only a handful of records are modified or created, but if the number is large, it could become significant. Or is there something in how an RDBMS handles triggers that would make it preferable to have a single trigger for all possible operations on a record? Something an old C++ programmer would miss if not informed about the peculiarities of database development. Did I miss something critical? My usual approach is to have functions remain as simple as practicable and do only one thing, unless there is a very good reason to have them more complex (in which a driver function that calls a number of simple functions may be preferable to one that tries to do everything). Simple functions are easy to validate, and once validated make validation of more complex driver functions easier.

Why bother with so many temporaries? Isn't that a waste of both development time (lots of extra typing and opportunity for errors such as typos) and runtime CPU cycles? Why not just insert or update values directly from the NEW or OLD record into the target table rather than copying the values first into the temporaries and then from the temporaries into their final destination?

HTH

Ted

----- Original Message -----
From: William Leite Araújo
To: Laura McCord
Cc: pgsql-general@postgresql.org
Sent: Tuesday, February 13, 2007 12:19 PM
Subject: Re: [GENERAL] Having a problem with my stored procedure

2007/2/13, Laura McCord <mccordl@southwestern.edu>:
To make a long story short, I am archiving data from an original table
to a table I created. This is a third party web application that I am
doing this with, so I can't revise the structure/code of this
application. With this said, if the original table goes through an
insert or update action I want to replicate the information to my
archive table. I don't want to delete any articles from my archive
table so this is why I am not wanting to do anything based on a delete
action.

The only problem that I am facing is how to tell the function that I want to perform an update if an update occurred and an insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based on an insert or update.

Help, I've been stumped for two days.
Thanks in advance.

This is what I have so far:
CREATE TRIGGER archive_articles
AFTER INSERT OR UPDATE ON
news_content
EXECUTE PROCEDURE su_archive_articles();

CREATE OR REPLACE FUNCTION su_archive_articles()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
DECLARE
tmp_news_id CHARACTER varying(48);
tmp_title CHARACTER varying(100);
tmp_abstract CHARACTER varying(300);
tmp_news_story TEXT;
tmp_topic_id CHARACTER varying(10);
tmp_create_date DATE;
tmp_author CHARACTER varying(50);
tmp_begin_date DATE;
tmp_end_date DATE;
tmp_priority CHARACTER(1);
tmp_image_name CHARACTER varying(512);
tmp_image_mime_type CHARACTER varying(50);
tmp_layout_type CHARACTER varying(10);

BEGIN
SELECT INTO  tmp_news_id news_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_title title from news_content where last_inserted(news_id);
SELECT INTO  tmp_abstract abstract from news_content where last_inserted(news_id);
SELECT INTO  tmp_news_story news_story from news_content where last_inserted(news_id);
SELECT INTO  tmp_topic_id topic_id from news_content where last_inserted(news_id);
SELECT INTO  tmp_create_date create_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_author author from news_content where last_inserted(news_id);
SELECT INTO  tmp_begin_date begin_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_end_date end_date from news_content where last_inserted(news_id);
SELECT INTO  tmp_priority priority from news_content where last_inserted(news_id);
SELECT INTO  tmp_image_name image_name from news_content where last_inserted(news_id);
SELECT INTO  tmp_image_mime_type image_mime_type from news_content where last_inserted(news_id);
SELECT INTO  tmp_layout_type layout_type from news_content where last_inserted(news_id);

   IF TG_OP = 'INSERT' THEN

//This is to be done if an INSERT action was done on the table

INSERT INTO su_archives(news_id, title, abstract, news_story,
topic_id, create_date, author, begin_date, end_date, priority,
image_name, image_mime_type, layout_type) VALUES (tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
image_name ,tmp_image_mime_type,tmp_layout_type);

   ELSEIF TG_OP = 'UPDATE' THEN

//HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE

END IF;

RETURN NEW;
END
';

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/

--
William Leite Araújo
Analista de Banco de Dados - QualiConsult

Re: Having a problem with my stored procedure

From

Laura McCord

Date:

13 February 2007, 17:22:44

I tried doing two different triggers as you suggested but I kept getting
an error stating:

psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
for relation "news_content" already exists
psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
relation "news_content" already exists

So, I thought perhaps it couldn't be done.



Ted Byers wrote:
> Would it not be simpler to just create two trigger functions, one that
> acts on insert operations and a second that acts on update
> operations?  A 30 second glance at the Postgresql documentation showed
> me that it is possible to have more than one row level trigger for a
> given table, which implies the simpler options is possible.  This
> would make for a much simpler design and avoid a conditional block
> that would then be unnecessary.  This extra cost is, of course,
> trivial if only a handful of records are modified or created, but if
> the number is large, it could become significant.  Or is there
> something in how an RDBMS handles triggers that would make it
> preferable to have a single trigger for all possible operations on a
> record?  Something an old C++ programmer would miss if not informed
> about the peculiarities of database development.  Did I miss something
> critical?  My usual approach is to have functions remain as simple as
> practicable and do only one thing, unless there is a very good reason
> to have them more complex (in which a driver function that calls a
> number of simple functions may be preferable to one that tries to do
> everything).  Simple functions are easy to validate, and once
> validated make validation of more complex driver functions easier.
>
> Why bother with so many temporaries?  Isn't that a waste of both
> development time (lots of extra typing and opportunity for errors such
> as typos) and runtime CPU cycles?  Why not just insert or update
> values directly from the NEW or OLD record into the target table
> rather than copying the values first into the temporaries and then
> from the temporaries into their final destination?
>
> HTH
>
> Ted
>
>     ----- Original Message -----
>     *From:* William Leite Araújo <mailto:william.bh@gmail.com>
>     *To:* Laura McCord <mailto:mccordl@southwestern.edu>
>     *Cc:* pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>
>     *Sent:* Tuesday, February 13, 2007 12:19 PM
>     *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>
>     2007/2/13, Laura McCord <mccordl@southwestern.edu
>     <mailto:mccordl@southwestern.edu>>:
>
>         To make a long story short, I am archiving data from an
>         original table
>         to a table I created. This is a third party web application
>         that I am
>         doing this with, so I can't revise the structure/code of this
>         application. With this said, if the original table goes
>         through an
>         insert or update action I want to replicate the information to my
>         archive table. I don't want to delete any articles from my archive
>         table so this is why I am not wanting to do anything based on
>         a delete
>         action.
>
>         The only problem that I am facing is how to tell the function
>         that I want to perform an update if an update occurred and an
>         insert if an insert action occurred. I want to have different
>         actions occur depending on if the trigger was based on an
>         insert or update.
>
>         Help, I've been stumped for two days.
>         Thanks in advance.
>
>         This is what I have so far:
>         CREATE TRIGGER archive_articles
>         AFTER INSERT OR UPDATE ON
>         news_content
>         EXECUTE PROCEDURE su_archive_articles();
>
>
>
>         CREATE OR REPLACE FUNCTION su_archive_articles()
>         RETURNS TRIGGER
>         LANGUAGE plpgsql
>         AS '
>         DECLARE
>         tmp_news_id CHARACTER varying(48);
>         tmp_title CHARACTER varying(100);
>         tmp_abstract CHARACTER varying(300);
>         tmp_news_story TEXT;
>         tmp_topic_id CHARACTER varying(10);
>         tmp_create_date DATE;
>         tmp_author CHARACTER varying(50);
>         tmp_begin_date DATE;
>         tmp_end_date DATE;
>         tmp_priority CHARACTER(1);
>         tmp_image_name CHARACTER varying(512);
>         tmp_image_mime_type CHARACTER varying(50);
>         tmp_layout_type CHARACTER varying(10);
>
>         BEGIN
>         SELECT INTO  tmp_news_id news_id from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_title title from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_abstract abstract from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_news_story news_story from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_topic_id topic_id from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_create_date create_date from news_content
>         where last_inserted(news_id);
>         SELECT INTO  tmp_author author from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_begin_date begin_date from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_end_date end_date from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_priority priority from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_image_name image_name from news_content where
>         last_inserted(news_id);
>         SELECT INTO  tmp_image_mime_type image_mime_type from
>         news_content where last_inserted(news_id);
>         SELECT INTO  tmp_layout_type layout_type from news_content
>         where last_inserted(news_id);
>
>
>        IF TG_OP = 'INSERT' THEN
>
>         //This is to be done if an INSERT action was done on the table
>
>         INSERT INTO su_archives(news_id, title, abstract, news_story,
>         topic_id, create_date, author, begin_date, end_date, priority,
>         image_name, image_mime_type, layout_type) VALUES
>
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
>
>         image_name ,tmp_image_mime_type,tmp_layout_type);
>
>
>        ELSEIF  TG_OP = 'UPDATE' THEN
>
>         //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN
>         UPDATE WAS DONE
>
>
>         END IF;
>
>         RETURN NEW;
>         END
>         ';
>
>         ---------------------------(end of
>         broadcast)---------------------------
>         TIP 4: Have you searched our list archives?
>
>                        http://archives.postgresql.org/
>         <http://archives.postgresql.org/>
>
>
>
>
>     --
>     William Leite Araújo
>     Analista de Banco de Dados - QualiConsult
>

Re: Having a problem with my stored procedure

From

Laura McCord

Date:

13 February 2007, 17:48:04

About your last comment, I can't do any revisions of the third party
application where the inserts and updates are occurring. Plus, this
whole idea came from a workaround based on a glitch in the software
where the expiration of articles is not occurring ,therefore I have to
do a delete articles to prevent them from being displayed on the web
interface. Also, I don't entirely want to get rid of them completely and
that is why I am saving records in an archive table to be used in
another application that I need to write in the future. So, this is the
reason for the redundancy.....it's a long story.

-Laura


Laura McCord wrote:
> I tried doing two different triggers as you suggested but I kept getting
> an error stating:
>
> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
> for relation "news_content" already exists
> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
> relation "news_content" already exists
>
> So, I thought perhaps it couldn't be done.
>
>
>
> Ted Byers wrote:
>
>> Would it not be simpler to just create two trigger functions, one that
>> acts on insert operations and a second that acts on update
>> operations?  A 30 second glance at the Postgresql documentation showed
>> me that it is possible to have more than one row level trigger for a
>> given table, which implies the simpler options is possible.  This
>> would make for a much simpler design and avoid a conditional block
>> that would then be unnecessary.  This extra cost is, of course,
>> trivial if only a handful of records are modified or created, but if
>> the number is large, it could become significant.  Or is there
>> something in how an RDBMS handles triggers that would make it
>> preferable to have a single trigger for all possible operations on a
>> record?  Something an old C++ programmer would miss if not informed
>> about the peculiarities of database development.  Did I miss something
>> critical?  My usual approach is to have functions remain as simple as
>> practicable and do only one thing, unless there is a very good reason
>> to have them more complex (in which a driver function that calls a
>> number of simple functions may be preferable to one that tries to do
>> everything).  Simple functions are easy to validate, and once
>> validated make validation of more complex driver functions easier.
>>
>> Why bother with so many temporaries?  Isn't that a waste of both
>> development time (lots of extra typing and opportunity for errors such
>> as typos) and runtime CPU cycles?  Why not just insert or update
>> values directly from the NEW or OLD record into the target table
>> rather than copying the values first into the temporaries and then
>> from the temporaries into their final destination?
>>
>> HTH
>>
>> Ted
>>
>>     ----- Original Message -----
>>     *From:* William Leite Araújo <mailto:william.bh@gmail.com>
>>     *To:* Laura McCord <mailto:mccordl@southwestern.edu>
>>     *Cc:* pgsql-general@postgresql.org
>>     <mailto:pgsql-general@postgresql.org>
>>     *Sent:* Tuesday, February 13, 2007 12:19 PM
>>     *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>>
>>     2007/2/13, Laura McCord <mccordl@southwestern.edu
>>     <mailto:mccordl@southwestern.edu>>:
>>
>>         To make a long story short, I am archiving data from an
>>         original table
>>         to a table I created. This is a third party web application
>>         that I am
>>         doing this with, so I can't revise the structure/code of this
>>         application. With this said, if the original table goes
>>         through an
>>         insert or update action I want to replicate the information to my
>>         archive table. I don't want to delete any articles from my archive
>>         table so this is why I am not wanting to do anything based on
>>         a delete
>>         action.
>>
>>         The only problem that I am facing is how to tell the function
>>         that I want to perform an update if an update occurred and an
>>         insert if an insert action occurred. I want to have different
>>         actions occur depending on if the trigger was based on an
>>         insert or update.
>>
>>         Help, I've been stumped for two days.
>>         Thanks in advance.
>>
>>         This is what I have so far:
>>         CREATE TRIGGER archive_articles
>>         AFTER INSERT OR UPDATE ON
>>         news_content
>>         EXECUTE PROCEDURE su_archive_articles();
>>
>>
>>
>>         CREATE OR REPLACE FUNCTION su_archive_articles()
>>         RETURNS TRIGGER
>>         LANGUAGE plpgsql
>>         AS '
>>         DECLARE
>>         tmp_news_id CHARACTER varying(48);
>>         tmp_title CHARACTER varying(100);
>>         tmp_abstract CHARACTER varying(300);
>>         tmp_news_story TEXT;
>>         tmp_topic_id CHARACTER varying(10);
>>         tmp_create_date DATE;
>>         tmp_author CHARACTER varying(50);
>>         tmp_begin_date DATE;
>>         tmp_end_date DATE;
>>         tmp_priority CHARACTER(1);
>>         tmp_image_name CHARACTER varying(512);
>>         tmp_image_mime_type CHARACTER varying(50);
>>         tmp_layout_type CHARACTER varying(10);
>>
>>         BEGIN
>>         SELECT INTO  tmp_news_id news_id from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_title title from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_abstract abstract from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_news_story news_story from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_topic_id topic_id from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_create_date create_date from news_content
>>         where last_inserted(news_id);
>>         SELECT INTO  tmp_author author from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_begin_date begin_date from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_end_date end_date from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_priority priority from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_image_name image_name from news_content where
>>         last_inserted(news_id);
>>         SELECT INTO  tmp_image_mime_type image_mime_type from
>>         news_content where last_inserted(news_id);
>>         SELECT INTO  tmp_layout_type layout_type from news_content
>>         where last_inserted(news_id);
>>
>>
>>        IF TG_OP = 'INSERT' THEN
>>
>>         //This is to be done if an INSERT action was done on the table
>>
>>         INSERT INTO su_archives(news_id, title, abstract, news_story,
>>         topic_id, create_date, author, begin_date, end_date, priority,
>>         image_name, image_mime_type, layout_type) VALUES
>>
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
>>
>>         image_name ,tmp_image_mime_type,tmp_layout_type);
>>
>>
>>        ELSEIF  TG_OP = 'UPDATE' THEN
>>
>>         //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN
>>         UPDATE WAS DONE
>>
>>
>>         END IF;
>>
>>         RETURN NEW;
>>         END
>>         ';
>>
>>         ---------------------------(end of
>>         broadcast)---------------------------
>>         TIP 4: Have you searched our list archives?
>>
>>                        http://archives.postgresql.org/
>>         <http://archives.postgresql.org/>
>>
>>
>>
>>
>>     --
>>     William Leite Araújo
>>     Analista de Banco de Dados - QualiConsult
>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/
>

Re: Having a problem with my stored procedure

From

Tom Lane

Date:

13 February 2007, 18:24:07

Laura McCord <mccordl@southwestern.edu> writes:
> I tried doing two different triggers as you suggested but I kept getting
> an error stating:

> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
> for relation "news_content" already exists
> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
> relation "news_content" already exists

> So, I thought perhaps it couldn't be done.

You need to pick different names for the triggers ... or if you're
trying to replace an existing trigger definition, you need to DROP it first.

            regards, tom lane

Re: Having a problem with my stored procedure

From

Laura McCord

Date:

13 February 2007, 18:37:41

Here is a question that I am stumped on:

Does postgres even recognize last_inserted() as mysql does? I notice
that the function fails on that line.

Thanks,
 Laura

Tom Lane wrote:
> Laura McCord <mccordl@southwestern.edu> writes:
>
>> I tried doing two different triggers as you suggested but I kept getting
>> an error stating:
>>
>
>
>> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
>> for relation "news_content" already exists
>> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
>> relation "news_content" already exists
>>
>
>
>> So, I thought perhaps it couldn't be done.
>>
>
> You need to pick different names for the triggers ... or if you're
> trying to replace an existing trigger definition, you need to DROP it first.
>
>             regards, tom lane
>

Re: Having a problem with my stored procedure

From

Alan Hodgson

Date:

13 February 2007, 18:46:46

On Tuesday 13 February 2007 11:35, Laura McCord
<mccordl@southwestern.edu> wrote:
> Here is a question that I am stumped on:
>
> Does postgres even recognize last_inserted() as mysql does? I notice
> that the function fails on that line.
>

Not, that's just a MySQL function.  You could, however, look for the
last value of the sequence generator for the table's primary key, which
should be (but may not always be) equivalent.

However, you are doing far more work than necessary getting the
inserted/updated data; you can just access it through the OLD. and NEW.
records already provided to the trigger.

http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html

--
"If a nation expects to be ignorant and free, in a state of
civilization, it expects what never was and never will be." -- Thomas
Jefferson

Re: Having a problem with my stored procedure

From

Laura McCord

Date:

13 February 2007, 18:53:07

Ok, I think I am starting to put two-and-two together.... based on your
thread and Ted's thread, I just realized that OLD. and NEW. are keywords
in postgres.

Alan Hodgson wrote:
> On Tuesday 13 February 2007 11:35, Laura McCord
> <mccordl@southwestern.edu> wrote:
>
>> Here is a question that I am stumped on:
>>
>> Does postgres even recognize last_inserted() as mysql does? I notice
>> that the function fails on that line.
>>
>>
>
> Not, that's just a MySQL function.  You could, however, look for the
> last value of the sequence generator for the table's primary key, which
> should be (but may not always be) equivalent.
>
> However, you are doing far more work than necessary getting the
> inserted/updated data; you can just access it through the OLD. and NEW.
> records already provided to the trigger.
>
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html
>
>
>

Re: Having a problem with my stored procedure

From

"Ted"

Date:

13 February 2007, 18:53:56

On Feb 13, 1:20 pm, mcco...@southwestern.edu (Laura McCord) wrote:
> I tried doing two different triggers as you suggested but I kept getting
> an error stating:
>
> psql:archive_news_articles.sql:75: ERROR:  trigger "archive_articles"
> for relation "news_content" already exists
> psql:archive_news_articles.sql:80: ERROR:  trigger "update_archives" for
> relation "news_content" already exists
>
> So, I thought perhaps it couldn't be done.
>
>
>
>
>
> Ted Byers wrote:
> > Would it not be simpler to just create two trigger functions, one that
> > acts on insert operations and a second that acts on update
> > operations?  A 30 second glance at the Postgresql documentation showed
> > me that it is possible to have more than one row level trigger for a
> > given table, which implies the simpler options is possible.  This
> > would make for a much simpler design and avoid a conditional block
> > that would then be unnecessary.  This extra cost is, of course,
> > trivial if only a handful of records are modified or created, but if
> > the number is large, it could become significant.  Or is there
> > something in how an RDBMS handles triggers that would make it
> > preferable to have a single trigger for all possible operations on a
> > record?  Something an old C++ programmer would miss if not informed
> > about the peculiarities of database development.  Did I miss something
> > critical?  My usual approach is to have functions remain as simple as
> > practicable and do only one thing, unless there is a very good reason
> > to have them more complex (in which a driver function that calls a
> > number of simple functions may be preferable to one that tries to do
> > everything).  Simple functions are easy to validate, and once
> > validated make validation of more complex driver functions easier.
>
> > Why bother with so many temporaries?  Isn't that a waste of both
> > development time (lots of extra typing and opportunity for errors such
> > as typos) and runtime CPU cycles?  Why not just insert or update
> > values directly from the NEW or OLD record into the target table
> > rather than copying the values first into the temporaries and then
> > from the temporaries into their final destination?
>
> > HTH
>
> > Ted
>
> >     ----- Original Message -----
> >     *From:* William Leite Araújo <mailto:william...@gmail.com>
> >     *To:* Laura McCord <mailto:mcco...@southwestern.edu>
> >     *Cc:* pgsql-gene...@postgresql.org
> >     <mailto:pgsql-gene...@postgresql.org>
> >     *Sent:* Tuesday, February 13, 2007 12:19 PM
> >     *Subject:* Re: [GENERAL] Having a problem with my stored procedure
>
> >     2007/2/13, Laura McCord <mcco...@southwestern.edu
> >     <mailto:mcco...@southwestern.edu>>:
>
> >         To make a long story short, I am archiving data from an
> >         original table
> >         to a table I created. This is a third party web application
> >         that I am
> >         doing this with, so I can't revise the structure/code of this
> >         application. With this said, if the original table goes
> >         through an
> >         insert or update action I want to replicate the information to my
> >         archive table. I don't want to delete any articles from my archive
> >         table so this is why I am not wanting to do anything based on
> >         a delete
> >         action.
>
> >         The only problem that I am facing is how to tell the function
> >         that I want to perform an update if an update occurred and an
> >         insert if an insert action occurred. I want to have different
> >         actions occur depending on if the trigger was based on an
> >         insert or update.
>
> >         Help, I've been stumped for two days.
> >         Thanks in advance.
>
> >         This is what I have so far:
> >         CREATE TRIGGER archive_articles
> >         AFTER INSERT OR UPDATE ON
> >         news_content
> >         EXECUTE PROCEDURE su_archive_articles();
>
> >         CREATE OR REPLACE FUNCTION su_archive_articles()
> >         RETURNS TRIGGER
> >         LANGUAGE plpgsql
> >         AS '
> >         DECLARE
> >         tmp_news_id CHARACTER varying(48);
> >         tmp_title CHARACTER varying(100);
> >         tmp_abstract CHARACTER varying(300);
> >         tmp_news_story TEXT;
> >         tmp_topic_id CHARACTER varying(10);
> >         tmp_create_date DATE;
> >         tmp_author CHARACTER varying(50);
> >         tmp_begin_date DATE;
> >         tmp_end_date DATE;
> >         tmp_priority CHARACTER(1);
> >         tmp_image_name CHARACTER varying(512);
> >         tmp_image_mime_type CHARACTER varying(50);
> >         tmp_layout_type CHARACTER varying(10);
>
> >         BEGIN
> >         SELECT INTO  tmp_news_id news_id from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_title title from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_abstract abstract from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_news_story news_story from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_topic_id topic_id from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_create_date create_date from news_content
> >         where last_inserted(news_id);
> >         SELECT INTO  tmp_author author from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_begin_date begin_date from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_end_date end_date from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_priority priority from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_image_name image_name from news_content where
> >         last_inserted(news_id);
> >         SELECT INTO  tmp_image_mime_type image_mime_type from
> >         news_content where last_inserted(news_id);
> >         SELECT INTO  tmp_layout_type layout_type from news_content
> >         where last_inserted(news_id);
>
> >        IF TG_OP = 'INSERT' THEN
>
> >         //This is to be done if an INSERT action was done on the table
>
> >         INSERT INTO su_archives(news_id, title, abstract, news_story,
> >         topic_id, create_date, author, begin_date, end_date, priority,
> >         image_name, image_mime_type, layout_type) VALUES
> >
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
>
> >         image_name ,tmp_image_mime_type,tmp_layout_type);
>
> >        ELSEIF  TG_OP = 'UPDATE' THEN
>
> >         //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN
> >         UPDATE WAS DONE
>
> >         END IF;
>
> >         RETURN NEW;
> >         END
> >         ';
>
> >         ---------------------------(end of
> >         broadcast)---------------------------
> >         TIP 4: Have you searched our list archives?
>
> >                        http://archives.postgresql.org/
> >         <http://archives.postgresql.org/>
>
> >     --
> >     William Leite Araújo
> >     Analista de Banco de Dados - QualiConsult
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/- Hide quoted text -
>
> - Show quoted text -

The following doesn't give me any errors (I show here everything
required to set up my test example).  Mind you, I haven't gone all the
way to thoroughly test it for all possible ways it could fail, but it
does show that you can create separate trigger functions for each kind
of action.  Maybe you encountered a name collision in the function or
trigger names you tried using?  Perhaps someone can find where you
went awry if you posted the code that generated the errors you
encountered.

CREATE SEQUENCE tests.a_test_sequence
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE tests.a_test_sequence OWNER TO postgres;

CREATE TABLE tests.Insert_archive
(
  id int8 NOT NULL,
  dummy_data varchar(1024) NOT NULL,
  when_action_taken timestamp DEFAULT now()
)
WITHOUT OIDS;
ALTER TABLE tests.Insert_archive OWNER TO postgres;

CREATE TABLE tests.update_archive
(
  ID int8 NOT NULL,
  dummy_data varchar(1024) NOT NULL,
  when_action_taken timestamp NOT NULL DEFAULT now()
)
WITHOUT OIDS;
ALTER TABLE tests.update_archive OWNER TO postgres;

CREATE OR REPLACE FUNCTION tests.insert_trigger()
  RETURNS "trigger" AS
$BODY$begin
INSERT INTO Insert_archive (ID,dummy_data) VALUES
(NEW.ID,NEW.dummy_data);
RETURN NEW;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION tests.insert_trigger() OWNER TO postgres;

CREATE OR REPLACE FUNCTION tests.update_trigger()
  RETURNS "trigger" AS
$BODY$begin
INSERT INTO update_archive (ID,dummy_data) VALUES
(NEW.ID,NEW.dummy_data);
RETURN NEW;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION tests.update_trigger() OWNER TO postgres;


CREATE TABLE tests.a_test_table
(
  dummy_data varchar(1024) NOT NULL,
  id int8 DEFAULT nextval(('tests.a_test_sequence'::text)::regclass)
)
WITHOUT OIDS;
ALTER TABLE tests.a_test_table OWNER TO postgres;

CREATE TRIGGER an_insert_trigger
  BEFORE INSERT
  ON tests.a_test_table
  FOR EACH ROW
  EXECUTE PROCEDURE tests.insert_trigger();

CREATE TRIGGER an_update_trigger
  BEFORE UPDATE
  ON tests.a_test_table
  FOR EACH ROW
  EXECUTE PROCEDURE tests.update_trigger();

HTH

Ted

Re: Having a problem with my stored procedure

From

"Ted"

Date:

13 February 2007, 19:04:11

On Feb 13, 1:45 pm, mcco...@southwestern.edu (Laura McCord) wrote:
> About your last comment, I can't do any revisions of the third party
> application where the inserts and updates are occurring. Plus, this
> whole idea came from a workaround based on a glitch in the software
> where the expiration of articles is not occurring ,therefore I have to
> do a delete articles to prevent them from being displayed on the web
> interface. Also, I don't entirely want to get rid of them completely and
> that is why I am saving records in an archive table to be used in
> another application that I need to write in the future. So, this is the
> reason for the redundancy.....it's a long story.
>
> -Laura
>

I don't think I was suggesting you do anything with the original
application.  Rather, I was suggesting you alter what you're were
already trying to do.  You can apparently create as many trigger
functions as you like (implied by what I have read in the PostgreSQL
documentation), presumably without name collisions though, and you
have absolute control of what you do within the functions you develop,
as long as it is syntactically correct.  So far, I see nothing
preventing you from writing two trigger functions or requiring you to
use so many temporaries.   How does not touching the third party
application affect the triggers you add to the database?

Cheers

Ted

Re: Having a problem with my stored procedure

From

Alban Hertroys

Date:

14 February 2007, 09:33:30

Laura McCord wrote:
> To make a long story short, I am archiving data from an original table
> to a table I created. This is a third party web application that I am
> doing this with, so I can't revise the structure/code of this
> application. With this said, if the original table goes through an
> insert or update action I want to replicate the information to my
> archive table. I don't want to delete any articles from my archive
> table so this is why I am not wanting to do anything based on a delete
> action.
>
> The only problem that I am facing is how to tell the function that I want to perform an update if an update occurred
andan insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based
onan insert or update.  
>
> Help, I've been stumped for two days.
> Thanks in advance.
>
> This is what I have so far:
> CREATE TRIGGER archive_articles
> AFTER INSERT OR UPDATE ON
> news_content
> EXECUTE PROCEDURE su_archive_articles();

As suggested earlier, it is probably cleaner to define separate triggers
on insert and on update. That is possible, but they can't have the same
names.
You probably want to name them accordingly too, or you'll get naming
conflicts.

I suggest:
DROP TRIGGER archive_articles ON news_content;

CREATE TRIGGER archive_articles_insert AFTER INSERT ON news_content
EXECUTE PROCEDURE su_archive_articles_insert();

CREATE TRIGGER archive_articles_update AFTER UPDATE ON news_content
EXECUTE PROCEDURE su_archive_articles_update();


> CREATE OR REPLACE FUNCTION su_archive_articles()
> RETURNS TRIGGER
> LANGUAGE plpgsql
> AS '
> DECLARE
>  tmp_news_id CHARACTER varying(48);
>  tmp_title CHARACTER varying(100);
>  tmp_abstract CHARACTER varying(300);
>  tmp_news_story TEXT;
>  tmp_topic_id CHARACTER varying(10);
>  tmp_create_date DATE;
>  tmp_author CHARACTER varying(50);
>  tmp_begin_date DATE;
>  tmp_end_date DATE;
>  tmp_priority CHARACTER(1);
>  tmp_image_name CHARACTER varying(512);
>  tmp_image_mime_type CHARACTER varying(50);
>  tmp_layout_type CHARACTER varying(10);

It is far easier and just as valid to perform an INSERT or an UPDATE
with the values from the predefined NEW record, like this:

CREATE OR REPLACE FUNCTION su_archive_articles_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
BEGIN
    INSERT INTO su_archives (news_id, title, abstract)
    VALUES (NEW.news_id, NEW.title, NEW.abstract);
END;
';

I'm sure you can add the rest of the columns to that statement yourself.
The update trigger function is similar to this one, but with an UPDATE
statement of course.

In insert and update triggers there's always a record called NEW. In
delete and update triggers there's a record called OLD (and yes, that
means in update triggers you get both).

If you really have to go the path you took, may I suggest:

DECLARE
    news_rec su_archives%ROWTYPE;
BEGIN
    SELECT INTO news_rec news_id, title, abstract
    FROM news_content
    WHERE last_inserted NEW.news_id;




> BEGIN
> SELECT INTO  tmp_news_id news_id from news_content where last_inserted(news_id);
> SELECT INTO  tmp_title title from news_content where last_inserted(news_id);
> SELECT INTO  tmp_abstract abstract from news_content where last_inserted(news_id);
> SELECT INTO  tmp_news_story news_story from news_content where last_inserted(news_id);
> SELECT INTO  tmp_topic_id topic_id from news_content where last_inserted(news_id);
> SELECT INTO  tmp_create_date create_date from news_content where last_inserted(news_id);
> SELECT INTO  tmp_author author from news_content where last_inserted(news_id);
> SELECT INTO  tmp_begin_date begin_date from news_content where last_inserted(news_id);
> SELECT INTO  tmp_end_date end_date from news_content where last_inserted(news_id);
> SELECT INTO  tmp_priority priority from news_content where last_inserted(news_id);
> SELECT INTO  tmp_image_name image_name from news_content where last_inserted(news_id);
> SELECT INTO  tmp_image_mime_type image_mime_type from news_content where last_inserted(news_id);
> SELECT INTO  tmp_layout_type layout_type from news_content where last_inserted(news_id);
>
> //This is to be done if an INSERT action was done on the table
>
> INSERT INTO su_archives(news_id, title, abstract, news_story,
> topic_id, create_date, author, begin_date, end_date, priority,
> image_name, image_mime_type, layout_type) VALUES
(tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_
> image_name ,tmp_image_mime_type,tmp_layout_type);
>
> //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE
>
> RETURN NEW;
> END
> ';
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Re: Having a problem with my stored procedure

From

"Ted"

Date:

14 February 2007, 14:18:00

> As suggested earlier, it is probably cleaner to define separate triggers
> on insert and on update. That is possible, but they can't have the same
> names.
> You probably want to name them accordingly too, or you'll get naming
> conflicts.
>
> I suggest:
> DROP TRIGGER archive_articles ON news_content;
>
While this is something I'd do if I had absolute control over all the
software, in the OP's case, there is a third party application
involved that can't be modified.  I would therefore worry that the
developer of that software may have also placed triggers of that name
on that table.  This would suggest a little slop in the practices of
that developer (i.e. of the third party application, not the OP) since
a decent naming convention would make a name collision between that
developer's code and the OP's code highly unlikely, but that is
another issue.  If the OP is getting name collision when trying to
create these triggers, the implication is that the developer of the
third party app in fact defined triggers of the same names, so
dropping previously created triggers may well break that app.  It
seems to me that dropping something I haven't created is a high risk
action.

A naming convention similar to what I use would solve that problem
without the risk associated with dropping something someone else has
developed.  If I am working on something to be distributed, I use a
naming scheme that prepends a very short string that makes it clear
the code was developed by myself or one of my staff, and in languages
that support a namespace, such as C++, I make certain there is a
namespace ID unique to my organization.  This eliminates the risk of a
name collision unless some developer actually tries to impersonate
me.  In some cases, where I am working as part of a team, my
preference is to do the same with the developer's ID (since always the
developer who developed a given peice of code is responsible for
fixing any bugs in it whever possible).  Of course, always the fact is
fully documented, both in the code and in design documents provided to
the client.  This is a discipline I impose on myself, as a courtesy to
those who come after me, and it involves considerations any library
developer necessarily worries about.  It is not something I want to
impose on those who come after me, but which I would require of those
who develop libraries or databases or tools I need to use in order to
be productive.

Damn.  It just occured to me that the OP had to be able to see the
structure of the DB using a tool like pgAdmin, in order to just get
the names of the tables and columns.  Therefore, the OP should have
also been able to see the definitions of any existing triggers and
trigger functions.  Further, the OP should be able to create more
functions that could be called at the end of any existing trigger
functions, thereby obtaining the functionality desired without
compromizing the third party app.  You can edit these functions from
within pgAdmin.  I have done so myself on trigger functions I created
myself.  This would make the OP's task almost trivially simple.  Don't
you just hate when you see the obvious solution only after spending
time on other options?  ;-)

Cheers,

Ted

Re: Having a problem with my stored procedure

From

Laura McCord

Date:

14 February 2007, 14:47:22

I did check with customer support (they are actual developers of the
system not middle men) and they said there aren't any triggers in the db
structure so it's safe for me to include triggers. So, that helps me
feel reassured because you had a good point. So more than likely, it was
my error and I must have not dropped my trigger properly before
recreating it.

Thanks everyone for your advice. I am going to try this afternoon to
take your suggestions and make this work. I never had a problem creating
mysql stored procedures and I just learned that with postgres it is so
much different.

Thanks Again,
  Laura

Ted wrote:
>> As suggested earlier, it is probably cleaner to define separate triggers
>> on insert and on update. That is possible, but they can't have the same
>> names.
>> You probably want to name them accordingly too, or you'll get naming
>> conflicts.
>>
>> I suggest:
>> DROP TRIGGER archive_articles ON news_content;
>>
>>
> While this is something I'd do if I had absolute control over all the
> software, in the OP's case, there is a third party application
> involved that can't be modified.  I would therefore worry that the
> developer of that software may have also placed triggers of that name
> on that table.  This would suggest a little slop in the practices of
> that developer (i.e. of the third party application, not the OP) since
> a decent naming convention would make a name collision between that
> developer's code and the OP's code highly unlikely, but that is
> another issue.  If the OP is getting name collision when trying to
> create these triggers, the implication is that the developer of the
> third party app in fact defined triggers of the same names, so
> dropping previously created triggers may well break that app.  It
> seems to me that dropping something I haven't created is a high risk
> action.
>
> A naming convention similar to what I use would solve that problem
> without the risk associated with dropping something someone else has
> developed.  If I am working on something to be distributed, I use a
> naming scheme that prepends a very short string that makes it clear
> the code was developed by myself or one of my staff, and in languages
> that support a namespace, such as C++, I make certain there is a
> namespace ID unique to my organization.  This eliminates the risk of a
> name collision unless some developer actually tries to impersonate
> me.  In some cases, where I am working as part of a team, my
> preference is to do the same with the developer's ID (since always the
> developer who developed a given peice of code is responsible for
> fixing any bugs in it whever possible).  Of course, always the fact is
> fully documented, both in the code and in design documents provided to
> the client.  This is a discipline I impose on myself, as a courtesy to
> those who come after me, and it involves considerations any library
> developer necessarily worries about.  It is not something I want to
> impose on those who come after me, but which I would require of those
> who develop libraries or databases or tools I need to use in order to
> be productive.
>
> Damn.  It just occured to me that the OP had to be able to see the
> structure of the DB using a tool like pgAdmin, in order to just get
> the names of the tables and columns.  Therefore, the OP should have
> also been able to see the definitions of any existing triggers and
> trigger functions.  Further, the OP should be able to create more
> functions that could be called at the end of any existing trigger
> functions, thereby obtaining the functionality desired without
> compromizing the third party app.  You can edit these functions from
> within pgAdmin.  I have done so myself on trigger functions I created
> myself.  This would make the OP's task almost trivially simple.  Don't
> you just hate when you see the obvious solution only after spending
> time on other options?  ;-)
>
> Cheers,
>
> Ted
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>