Обсуждение: Trigger function which inserts into table; values from lookup
Not sure it the title of this post gives a clear message...I need to have a trigger function insert records into a table under certain conditions, where the values inserted are partially based on the results of a select query. table t_item item_id (pk) item_name item_org_id item_active table t_koaitem koaitem_id (pk) koaitem_koa_id koaitem_item_id table t_koa koa_id (pk) koa_name koa_active Any time a t_item record is created or updated, and item_active = true, I need to insert records referencing that item into t_koaitem. One record should be entered into t_koaitem for each t_koa record that has koa_active = true. The koa_id value in each t_koa rec would be inserted into koaitem_koa_id. And additional aspect is that there is a unique key on koaitem_koa_id and koaitem_item_id; and the pair of values being inserted may already exist in t_koaitem. I've not looked much yet but have not seen error trapping that would essentially handle the dupe key by skipping that insert and moving on to the next. I can post my own attempt but it lacks any error handling and does not work in general. If plpgsql is not the best lang for this I could use python. I know this is a lot to ask for but I'm all thumbs with postres function syntax. I've written simple functions and trigger functions but this one is rather stiff. -- View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10703268 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
So, I may have hammered out the basic trigger function. The error trapping
part is a complete mystery to me. I'll post the trigger function below in
the hopes that someone will at least comment on the error handling part.
The error, as expected is 'duplicate key violates unique contraint blah
blah" because the routine tries to append a rec for each item; most items
will already have a t_koaitem record. I am not looping through the tables
and evaluating the need to append a rec because I thought this approach
might be more efficient.
Something else I need to deal with is that when a new record is being
inserted, the old.item_active = false evalualtion blows up. I will probably
have that handled pretty soon but if anyone wishs to spell it out that'd be
great.
CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
DECLARE
rec_item record;
int_org_id integer;
BEGIN
-- whenever an item is set active; create entries in the following
table:
-- t_koaitem
if new.item_active = true and old.item_active = false
then
select * into rec_item from t_item
where item_id = new.item_id;
int_org_id = rec_item.item_org_id;
insert into t_koaitem (koai_koa_id, koai_item_id,
koai_item_locked, koai_user_idm)
SELECT t_koa.koa_id, t_item.item_id, false as lockstatus,
t_item.item_user_idm
FROM t_item INNER JOIN t_koa ON t_item.item_org_id =
t_koa.koa_org_id
WHERE (((t_item.item_active)=True)
AND ((t_koa.koa_koastatus_id)=2 Or
(t_koa.koa_koastatus_id)=3)
AND ((t_item.item_org_id)=int_org_id));
end if;
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context:
http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10709563
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Inching closer; the following handles the dupe key error but doesn't insert
the rows it should either. So, the exception is ending the insert, and not
continuing to insert for rows that don't violate the unique key restraint.
Is there a way around this or will I need to take a different approach?
CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
DECLARE
rec_item record;
int_org_id integer;
BEGIN
-- whenever an item is set active; create entries in the following
table:
-- t_koaitem
if new.item_active = true and old.item_active = false
then
select * into rec_item from t_item
where item_id = new.item_id;
int_org_id = rec_item.item_org_id;
BEGIN
insert into t_koaitem (koai_koa_id, koai_item_id,
koai_item_locked, koai_user_idm)
SELECT t_koa.koa_id, t_item.item_id, false as lockstatus,
t_item.item_user_idm
FROM t_item INNER JOIN t_koa ON t_item.item_org_id =
t_koa.koa_org_id
WHERE (((t_item.item_active)=True)
AND ((t_koa.koa_koastatus_id)=2 Or
(t_koa.koa_koastatus_id)=3)
AND ((t_item.item_org_id)=int_org_id));
EXCEPTION
when unique_violation then
-- do nothing?
END;
end if;
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
View this message in context:
http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10709966
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
novnov escribió: > > Inching closer; the following handles the dupe key error but doesn't insert > the rows it should either. So, the exception is ending the insert, and not > continuing to insert for rows that don't violate the unique key restraint. > Is there a way around this or will I need to take a different approach? You have to make the function return NEW, not NULL, if this is a BEFORE trigger. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
It's an after trigger. Any clue re my question? Alvaro Herrera-7 wrote: > > novnov escribió: >> >> Inching closer; the following handles the dupe key error but doesn't >> insert >> the rows it should either. So, the exception is ending the insert, and >> not >> continuing to insert for rows that don't violate the unique key >> restraint. >> Is there a way around this or will I need to take a different approach? > > You have to make the function return NEW, not NULL, if this is a BEFORE > trigger. > > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > > -- View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10711732 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
novnov <novnovice@gmail.com> writes:
> Any clue re my question?
You've placed the INSERT inside the BEGIN/EXCEPTION block, ergo it's
part of the work to be rolled back on exception.
regards, tom lane
OK, but, how do I set this up to do what I need? I want an insert that would create a dupe key to be rolled back, and inserts that would not create dupe keys to be committed. Tom Lane-2 wrote: > > novnov <novnovice@gmail.com> writes: >> Any clue re my question? > > You've placed the INSERT inside the BEGIN/EXCEPTION block, ergo it's > part of the work to be rolled back on exception. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > -- View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10713002 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 21/05/2007 05:26, novnov wrote: > OK, but, how do I set this up to do what I need? I want an insert that would > create a dupe key to be rolled back, and inserts that would not create dupe > keys to be committed. Do you specifically need it in a trigger? I seem to recall an example in the docs for pl/pgsql demonstrating a function to do something like this - I think it tries an INSERT, and when a duplicate key raises an exception, it does an update instead. - You could easily adapt this to your purposes. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
No and update would not be needed; but the capability would be close enough, I'd just skip the update, do nothing for that record. But from the sound of it, the example you're suggesting involves a loop or something of that order. I could have written this using a loop but thought a bulk operation that essentially worked like "insert new rows for the set and while doing so, silently skip inserts which would cause dupe key violations". I explained all of this in the earlier messages. I thought it might be more effenient to handle without a loop. I've been able to do this kind of thing with other databases; essentially instruct the routine to ignore errors silently, commit what it can commit. Raymond O'Donnell wrote: > > On 21/05/2007 05:26, novnov wrote: > >> OK, but, how do I set this up to do what I need? I want an insert that >> would >> create a dupe key to be rolled back, and inserts that would not create >> dupe >> keys to be committed. > > Do you specifically need it in a trigger? I seem to recall an example in > the docs for pl/pgsql demonstrating a function to do something like this > - I think it tries an INSERT, and when a duplicate key raises an > exception, it does an update instead. - You could easily adapt this to > your purposes. > > Ray. > > --------------------------------------------------------------- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > rod@iol.ie > --------------------------------------------------------------- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > > -- View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10720190 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Maybe you can use a "LEFT OUTER JOIN" ...
CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
DECLARE
rec_item record;
int_org_id integer;
BEGIN
-- whenever an item is set active; create entries in the following table:
-- t_koaitem
if new.item_active = true and old.item_active = false
then
select * into rec_item from t_item
where item_id = new.item_id;
int_org_id = rec_item.item_org_id;
insert into t_koaitem (koai_koa_id, koai_item_id, koai_item_locked, koai_user_idm)
SELECT t_koa.koa_id, t_item.item_id, false as lockstatus, t_item.item_user_idm
FROM t_item INNER JOIN t_koa ON t_item.item_org_id = t_koa.koa_org_id
LEFT OUTER JOIN t_koaitem ON (koaitem_koa_id = t_koa.koa_id AND koaitem_item_id = t_item.item_id)
WHERE (((t_item.item_active)=True)
AND ((t_koa.koa_koastatus_id)=2 Or (t_koa.koa_koastatus_id)=3)
AND ((t_item.item_org_id)=int_org_id)
AND (t_koaitem.koaitem_item_id IS NULL AND koaitem_item_id IS NULL)
);
end if;
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--
William Leite Araújo
Analista de Banco de Dados - QualiConsult
CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"()
RETURNS "pg_catalog"."trigger" AS
$BODY$
DECLARE
rec_item record;
int_org_id integer;
BEGIN
-- whenever an item is set active; create entries in the following table:
-- t_koaitem
if new.item_active = true and old.item_active = false
then
select * into rec_item from t_item
where item_id = new.item_id;
int_org_id = rec_item.item_org_id;
insert into t_koaitem (koai_koa_id, koai_item_id, koai_item_locked, koai_user_idm)
SELECT t_koa.koa_id, t_item.item_id, false as lockstatus, t_item.item_user_idm
FROM t_item INNER JOIN t_koa ON t_item.item_org_id = t_koa.koa_org_id
LEFT OUTER JOIN t_koaitem ON (koaitem_koa_id = t_koa.koa_id AND koaitem_item_id = t_item.item_id)
WHERE (((t_item.item_active)=True)
AND ((t_koa.koa_koastatus_id)=2 Or (t_koa.koa_koastatus_id)=3)
AND ((t_item.item_org_id)=int_org_id)
AND (t_koaitem.koaitem_item_id IS NULL AND koaitem_item_id IS NULL)
);
end if;
return null;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
2007/5/21, novnov <novnovice@gmail.com>:
No and update would not be needed; but the capability would be close enough,
I'd just skip the update, do nothing for that record.
But from the sound of it, the example you're suggesting involves a loop or
something of that order. I could have written this using a loop but thought
a bulk operation that essentially worked like "insert new rows for the set
and while doing so, silently skip inserts which would cause dupe key
violations". I explained all of this in the earlier messages. I thought it
might be more effenient to handle without a loop. I've been able to do this
kind of thing with other databases; essentially instruct the routine to
ignore errors silently, commit what it can commit.
Raymond O'Donnell wrote:
>
> On 21/05/2007 05:26, novnov wrote:
>
>> OK, but, how do I set this up to do what I need? I want an insert that
>> would
>> create a dupe key to be rolled back, and inserts that would not create
>> dupe
>> keys to be committed.
>
> Do you specifically need it in a trigger? I seem to recall an example in
> the docs for pl/pgsql demonstrating a function to do something like this
> - I think it tries an INSERT, and when a duplicate key raises an
> exception, it does an update instead. - You could easily adapt this to
> your purposes.
>
> Ray.
>
> ---------------------------------------------------------------
> Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
> rod@iol.ie
> ---------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
>
--
View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10720190
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(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
Yes, I think that would work and mabye I'll use that approach. But is there no way to implement as I orginally intended? Also, am I right in thinking that this approach is more efficient than a looping operation? William Leite Araújo wrote: > > Maybe you can use a "LEFT OUTER JOIN" ... > > CREATE or REPLACE FUNCTION "public"."tproc_handle_item_active"() > RETURNS "pg_catalog"."trigger" AS > $BODY$ > DECLARE > rec_item record; > int_org_id integer; > > BEGIN > > -- whenever an item is set active; create entries in the following > table: > -- t_koaitem > > if new.item_active = true and old.item_active = false > then > select * into rec_item from t_item > where item_id = new.item_id; > > int_org_id = rec_item.item_org_id; > > insert into t_koaitem (koai_koa_id, koai_item_id, > koai_item_locked, koai_user_idm) > SELECT t_koa.koa_id, t_item.item_id, false as lockstatus, > t_item.item_user_idm > FROM t_item INNER JOIN t_koa ON t_item.item_org_id = > t_koa.koa_org_id > LEFT OUTER JOIN t_koaitem ON (koaitem_koa_id = > t_koa.koa_id AND koaitem_item_id = t_item.item_id) > WHERE (((t_item.item_active)=True) > AND ((t_koa.koa_koastatus_id)=2 Or > (t_koa.koa_koastatus_id)=3) > AND ((t_item.item_org_id)=int_org_id) > AND (t_koaitem.koaitem_item_id IS NULL AND > koaitem_item_id > IS NULL) > ); > end if; > return null; > > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > 2007/5/21, novnov <novnovice@gmail.com>: >> >> >> No and update would not be needed; but the capability would be close >> enough, >> I'd just skip the update, do nothing for that record. >> >> But from the sound of it, the example you're suggesting involves a loop >> or >> something of that order. I could have written this using a loop but >> thought >> a bulk operation that essentially worked like "insert new rows for the >> set >> and while doing so, silently skip inserts which would cause dupe key >> violations". I explained all of this in the earlier messages. I thought >> it >> might be more effenient to handle without a loop. I've been able to do >> this >> kind of thing with other databases; essentially instruct the routine to >> ignore errors silently, commit what it can commit. >> >> >> Raymond O'Donnell wrote: >> > >> > On 21/05/2007 05:26, novnov wrote: >> > >> >> OK, but, how do I set this up to do what I need? I want an insert that >> >> would >> >> create a dupe key to be rolled back, and inserts that would not create >> >> dupe >> >> keys to be committed. >> > >> > Do you specifically need it in a trigger? I seem to recall an example >> in >> > the docs for pl/pgsql demonstrating a function to do something like >> this >> > - I think it tries an INSERT, and when a duplicate key raises an >> > exception, it does an update instead. - You could easily adapt this to >> > your purposes. >> > >> > Ray. >> > >> > --------------------------------------------------------------- >> > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland >> > rod@iol.ie >> > --------------------------------------------------------------- >> > >> > ---------------------------(end of >> broadcast)--------------------------- >> > TIP 2: Don't 'kill -9' the postmaster >> > >> > >> >> -- >> View this message in context: >> http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10720190 >> Sent from the PostgreSQL - general mailing list archive at Nabble.com. >> >> >> ---------------------------(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 > > -- View this message in context: http://www.nabble.com/Trigger-function-which-inserts-into-table--values-from-lookup-tf3784731.html#a10721651 Sent from the PostgreSQL - general mailing list archive at Nabble.com.