Re: Trigger function which inserts into table; values from lookup

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(at)gmail(dot)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(at)iol(dot)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