Trigger function which inserts into table; values from lookup

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;

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.