Re: Having a problem with my stored procedure - Mailing list pgsql-general

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/
>