Help, Can't figure out what is wrong with my stored procedure

[loulou2u <mccordl@xxxxxxxxxxxxxxxx>]

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

Help.

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, l
ayout_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
';

-- 
View this message in context: http://www.nabble.com/Help%2C-Can%27t-figure-out-what-is-wrong-with-my-stored-procedure-tf3221483.html#a8947077
Sent from the PostgreSQL - general mailing list archive at Nabble.com.