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

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