Re: Having a problem with my stored procedure

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(dot)bh(at)gmail(dot)com>
>> *To:* Laura McCord <mailto:mccordl(at)southwestern(dot)edu>
>> *Cc:* pgsql-general(at)postgresql(dot)org
>> <mailto:pgsql-general(at)postgresql(dot)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(at)southwestern(dot)edu
>> <mailto:mccordl(at)southwestern(dot)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/
>