Thank you for your suggestion which solved the problem. Much better solution that what I was trying to accomplish. Much smaller table to query since it only has one entry per user.
Clifford On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 12/28/2016 07:06 PM, Clifford Snow wrote: > >> I'm trying to write a trigger (my first) to update another table if the >> user_id is new. But I'm getting a index exception that the user_id >> > > What is the actual error message? > > already exists. I'm picking up data from another feed which gives >> provides me with changes to the main database. >> >> what I have is >> >> CREATE OR REPLACE FUNCTION add_new_user() >> RETURNS TRIGGER AS >> $BODY$ >> DECLARE >> commits RECORD; >> BEGIN >> SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id; >> > > In the above you are checking whether the changes table has the user_id > and if does not then creating a new user in the user table below. Not sure > how they are related, but from the description of the error it would seem > they are not that tightly coupled. In other words just because the user_id > does not exist in changes does not ensure it also absent from the table > user. Off the top of head I would say the below might be a better query: > > SELECT INTO commits * FROM user WHERE user_id = NEW.user_id; > > Though it would help the debugging process if you showed the complete > schema for both the changes and user tables. > > > IF NOT FOUND >> THEN >> INSERT INTO user (user_name, user_id, change_id, >> created_date) >> VALUES(NEW.user_name, NEW.user_id, >> NEW.change_id, NEW.created_date); >> END IF; >> RETURN NEW; >> END; >> $BODY$ >> LANGUAGE plpgsql; >> >> CREATE TRIGGER add_new_user_trigger >> BEFORE INSERT ON changes >> FOR EACH ROW >> EXECUTE PROCEDURE add_new_user(); >> >> I hoping for some recommendations on how to fix or at where I'm going >> wrong. >> >> Thanks, >> Clifford >> >> >> -- >> @osm_seattle >> osm_seattle.snowandsnow.us <http://osm_seattle.snowandsnow.us> >> OpenStreetMap: Maps with a human touch >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- @osm_seattle osm_seattle.snowandsnow.us OpenStreetMap: Maps with a human touch
