Re: Having a problem with my stored procedure

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