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

On Feb 13, 1:20 pm, mcco...@southwestern.edu (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...@gmail.com>
> >     *To:* Laura McCord <mailto:mcco...@southwestern.edu>
> >     *Cc:* pgsql-gene...@postgresql.org
> >     <mailto:pgsql-gene...@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 <mcco...@southwestern.edu
> >     <mailto:mcco...@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/
> >         <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/- Hide quoted text -
>
> - Show quoted text -

The following doesn't give me any errors (I show here everything
required to set up my test example).  Mind you, I haven't gone all the
way to thoroughly test it for all possible ways it could fail, but it
does show that you can create separate trigger functions for each kind
of action.  Maybe you encountered a name collision in the function or
trigger names you tried using?  Perhaps someone can find where you
went awry if you posted the code that generated the errors you
encountered.

CREATE SEQUENCE tests.a_test_sequence
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE tests.a_test_sequence OWNER TO postgres;

CREATE TABLE tests.Insert_archive
(
  id int8 NOT NULL,
  dummy_data varchar(1024) NOT NULL,
  when_action_taken timestamp DEFAULT now()
)
WITHOUT OIDS;
ALTER TABLE tests.Insert_archive OWNER TO postgres;

CREATE TABLE tests.update_archive
(
  ID int8 NOT NULL,
  dummy_data varchar(1024) NOT NULL,
  when_action_taken timestamp NOT NULL DEFAULT now()
)
WITHOUT OIDS;
ALTER TABLE tests.update_archive OWNER TO postgres;

CREATE OR REPLACE FUNCTION tests.insert_trigger()
  RETURNS "trigger" AS
$BODY$begin
INSERT INTO Insert_archive (ID,dummy_data) VALUES
(NEW.ID,NEW.dummy_data);
RETURN NEW;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION tests.insert_trigger() OWNER TO postgres;

CREATE OR REPLACE FUNCTION tests.update_trigger()
  RETURNS "trigger" AS
$BODY$begin
INSERT INTO update_archive (ID,dummy_data) VALUES
(NEW.ID,NEW.dummy_data);
RETURN NEW;
end;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION tests.update_trigger() OWNER TO postgres;


CREATE TABLE tests.a_test_table
(
  dummy_data varchar(1024) NOT NULL,
  id int8 DEFAULT nextval(('tests.a_test_sequence'::text)::regclass)
)
WITHOUT OIDS;
ALTER TABLE tests.a_test_table OWNER TO postgres;

CREATE TRIGGER an_insert_trigger
  BEFORE INSERT
  ON tests.a_test_table
  FOR EACH ROW
  EXECUTE PROCEDURE tests.insert_trigger();

CREATE TRIGGER an_update_trigger
  BEFORE UPDATE
  ON tests.a_test_table
  FOR EACH ROW
  EXECUTE PROCEDURE tests.update_trigger();

HTH

Ted