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

Laura McCord wrote:
> 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
andan insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based
onan 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();

As suggested earlier, it is probably cleaner to define separate triggers
on insert and on update. That is possible, but they can't have the same
names.
You probably want to name them accordingly too, or you'll get naming
conflicts.

I suggest:
DROP TRIGGER archive_articles ON news_content;

CREATE TRIGGER archive_articles_insert AFTER INSERT ON news_content
EXECUTE PROCEDURE su_archive_articles_insert();

CREATE TRIGGER archive_articles_update AFTER UPDATE ON news_content
EXECUTE PROCEDURE su_archive_articles_update();


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

It is far easier and just as valid to perform an INSERT or an UPDATE
with the values from the predefined NEW record, like this:

CREATE OR REPLACE FUNCTION su_archive_articles_insert()
RETURNS TRIGGER
LANGUAGE plpgsql
AS '
BEGIN
    INSERT INTO su_archives (news_id, title, abstract)
    VALUES (NEW.news_id, NEW.title, NEW.abstract);
END;
';

I'm sure you can add the rest of the columns to that statement yourself.
The update trigger function is similar to this one, but with an UPDATE
statement of course.

In insert and update triggers there's always a record called NEW. In
delete and update triggers there's a record called OLD (and yes, that
means in update triggers you get both).

If you really have to go the path you took, may I suggest:

DECLARE
    news_rec su_archives%ROWTYPE;
BEGIN
    SELECT INTO news_rec news_id, title, abstract
    FROM news_content
    WHERE last_inserted NEW.news_id;




> 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, 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);
>
> //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE
>
> RETURN NEW;
> END
> ';
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org/


--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //