Обсуждение: Having a problem with my stored procedure
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(); 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, 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 ';
2007/2/13, Laura McCord <mccordl@southwestern.edu>:
IF TG_OP = 'INSERT' THEN
ELSEIF TG_OP = 'UPDATE' THEN
END IF;
--
William Leite Araújo
Analista de Banco de Dados - QualiConsult
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
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újoTo: Laura McCordSent: Tuesday, February 13, 2007 12:19 PMSubject: Re: [GENERAL] Having a problem with my stored procedure2007/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
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.bh@gmail.com> > *To:* Laura McCord <mailto:mccordl@southwestern.edu> > *Cc:* pgsql-general@postgresql.org > <mailto: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 > <mailto: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/ > <http://archives.postgresql.org/> > > > > > -- > William Leite Araújo > Analista de Banco de Dados - QualiConsult >
About your last comment, I can't do any revisions of the third party application where the inserts and updates are occurring. Plus, this whole idea came from a workaround based on a glitch in the software where the expiration of articles is not occurring ,therefore I have to do a delete articles to prevent them from being displayed on the web interface. Also, I don't entirely want to get rid of them completely and that is why I am saving records in an archive table to be used in another application that I need to write in the future. So, this is the reason for the redundancy.....it's a long story. -Laura 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.bh@gmail.com> >> *To:* Laura McCord <mailto:mccordl@southwestern.edu> >> *Cc:* pgsql-general@postgresql.org >> <mailto: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 >> <mailto: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/ >> <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/ >
Laura McCord <mccordl@southwestern.edu> writes:
> 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.
You need to pick different names for the triggers ... or if you're
trying to replace an existing trigger definition, you need to DROP it first.
regards, tom lane
Here is a question that I am stumped on: Does postgres even recognize last_inserted() as mysql does? I notice that the function fails on that line. Thanks, Laura Tom Lane wrote: > Laura McCord <mccordl@southwestern.edu> writes: > >> 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. >> > > You need to pick different names for the triggers ... or if you're > trying to replace an existing trigger definition, you need to DROP it first. > > regards, tom lane >
On Tuesday 13 February 2007 11:35, Laura McCord <mccordl@southwestern.edu> wrote: > Here is a question that I am stumped on: > > Does postgres even recognize last_inserted() as mysql does? I notice > that the function fails on that line. > Not, that's just a MySQL function. You could, however, look for the last value of the sequence generator for the table's primary key, which should be (but may not always be) equivalent. However, you are doing far more work than necessary getting the inserted/updated data; you can just access it through the OLD. and NEW. records already provided to the trigger. http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html -- "If a nation expects to be ignorant and free, in a state of civilization, it expects what never was and never will be." -- Thomas Jefferson
Ok, I think I am starting to put two-and-two together.... based on your thread and Ted's thread, I just realized that OLD. and NEW. are keywords in postgres. Alan Hodgson wrote: > On Tuesday 13 February 2007 11:35, Laura McCord > <mccordl@southwestern.edu> wrote: > >> Here is a question that I am stumped on: >> >> Does postgres even recognize last_inserted() as mysql does? I notice >> that the function fails on that line. >> >> > > Not, that's just a MySQL function. You could, however, look for the > last value of the sequence generator for the table's primary key, which > should be (but may not always be) equivalent. > > However, you are doing far more work than necessary getting the > inserted/updated data; you can just access it through the OLD. and NEW. > records already provided to the trigger. > > http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html > > >
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
On Feb 13, 1:45 pm, mcco...@southwestern.edu (Laura McCord) wrote: > About your last comment, I can't do any revisions of the third party > application where the inserts and updates are occurring. Plus, this > whole idea came from a workaround based on a glitch in the software > where the expiration of articles is not occurring ,therefore I have to > do a delete articles to prevent them from being displayed on the web > interface. Also, I don't entirely want to get rid of them completely and > that is why I am saving records in an archive table to be used in > another application that I need to write in the future. So, this is the > reason for the redundancy.....it's a long story. > > -Laura > I don't think I was suggesting you do anything with the original application. Rather, I was suggesting you alter what you're were already trying to do. You can apparently create as many trigger functions as you like (implied by what I have read in the PostgreSQL documentation), presumably without name collisions though, and you have absolute control of what you do within the functions you develop, as long as it is syntactically correct. So far, I see nothing preventing you from writing two trigger functions or requiring you to use so many temporaries. How does not touching the third party application affect the triggers you add to the database? Cheers Ted
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 //
> 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; > While this is something I'd do if I had absolute control over all the software, in the OP's case, there is a third party application involved that can't be modified. I would therefore worry that the developer of that software may have also placed triggers of that name on that table. This would suggest a little slop in the practices of that developer (i.e. of the third party application, not the OP) since a decent naming convention would make a name collision between that developer's code and the OP's code highly unlikely, but that is another issue. If the OP is getting name collision when trying to create these triggers, the implication is that the developer of the third party app in fact defined triggers of the same names, so dropping previously created triggers may well break that app. It seems to me that dropping something I haven't created is a high risk action. A naming convention similar to what I use would solve that problem without the risk associated with dropping something someone else has developed. If I am working on something to be distributed, I use a naming scheme that prepends a very short string that makes it clear the code was developed by myself or one of my staff, and in languages that support a namespace, such as C++, I make certain there is a namespace ID unique to my organization. This eliminates the risk of a name collision unless some developer actually tries to impersonate me. In some cases, where I am working as part of a team, my preference is to do the same with the developer's ID (since always the developer who developed a given peice of code is responsible for fixing any bugs in it whever possible). Of course, always the fact is fully documented, both in the code and in design documents provided to the client. This is a discipline I impose on myself, as a courtesy to those who come after me, and it involves considerations any library developer necessarily worries about. It is not something I want to impose on those who come after me, but which I would require of those who develop libraries or databases or tools I need to use in order to be productive. Damn. It just occured to me that the OP had to be able to see the structure of the DB using a tool like pgAdmin, in order to just get the names of the tables and columns. Therefore, the OP should have also been able to see the definitions of any existing triggers and trigger functions. Further, the OP should be able to create more functions that could be called at the end of any existing trigger functions, thereby obtaining the functionality desired without compromizing the third party app. You can edit these functions from within pgAdmin. I have done so myself on trigger functions I created myself. This would make the OP's task almost trivially simple. Don't you just hate when you see the obvious solution only after spending time on other options? ;-) Cheers, Ted
I did check with customer support (they are actual developers of the system not middle men) and they said there aren't any triggers in the db structure so it's safe for me to include triggers. So, that helps me feel reassured because you had a good point. So more than likely, it was my error and I must have not dropped my trigger properly before recreating it. Thanks everyone for your advice. I am going to try this afternoon to take your suggestions and make this work. I never had a problem creating mysql stored procedures and I just learned that with postgres it is so much different. Thanks Again, Laura Ted wrote: >> 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; >> >> > While this is something I'd do if I had absolute control over all the > software, in the OP's case, there is a third party application > involved that can't be modified. I would therefore worry that the > developer of that software may have also placed triggers of that name > on that table. This would suggest a little slop in the practices of > that developer (i.e. of the third party application, not the OP) since > a decent naming convention would make a name collision between that > developer's code and the OP's code highly unlikely, but that is > another issue. If the OP is getting name collision when trying to > create these triggers, the implication is that the developer of the > third party app in fact defined triggers of the same names, so > dropping previously created triggers may well break that app. It > seems to me that dropping something I haven't created is a high risk > action. > > A naming convention similar to what I use would solve that problem > without the risk associated with dropping something someone else has > developed. If I am working on something to be distributed, I use a > naming scheme that prepends a very short string that makes it clear > the code was developed by myself or one of my staff, and in languages > that support a namespace, such as C++, I make certain there is a > namespace ID unique to my organization. This eliminates the risk of a > name collision unless some developer actually tries to impersonate > me. In some cases, where I am working as part of a team, my > preference is to do the same with the developer's ID (since always the > developer who developed a given peice of code is responsible for > fixing any bugs in it whever possible). Of course, always the fact is > fully documented, both in the code and in design documents provided to > the client. This is a discipline I impose on myself, as a courtesy to > those who come after me, and it involves considerations any library > developer necessarily worries about. It is not something I want to > impose on those who come after me, but which I would require of those > who develop libraries or databases or tools I need to use in order to > be productive. > > Damn. It just occured to me that the OP had to be able to see the > structure of the DB using a tool like pgAdmin, in order to just get > the names of the tables and columns. Therefore, the OP should have > also been able to see the definitions of any existing triggers and > trigger functions. Further, the OP should be able to create more > functions that could be called at the end of any existing trigger > functions, thereby obtaining the functionality desired without > compromizing the third party app. You can edit these functions from > within pgAdmin. I have done so myself on trigger functions I created > myself. This would make the OP's task almost trivially simple. Don't > you just hate when you see the obvious solution only after spending > time on other options? ;-) > > Cheers, > > Ted > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >