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

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.