From: | novnov <novnovice(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Trigger function which inserts into table; values from lookup |
Date: | 2007-05-20 06:34:27 |
Message-ID: | 10703268.post@talk.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Lopatic | 2007-05-20 10:01:46 | Re: Data replication through disk replication |
Previous Message | Ivan Zolotukhin | 2007-05-20 05:34:56 | Re: tsearch2 keep throw-away characters |