I have a database - PostgreSQL, that I administrate using pgadmin. I have a trigger function:
CREATE OR REPLACE FUNCTION household.before_b_member() RETURNS trigger AS $BODY$DECLARE duplicate integer; BEGIN RAISE NOTICE 'trigger start'; IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN IF (NEW.odk_key is not null) THEN RAISE NOTICE 'checking for duplicates'; duplicate := (SELECT household.b_member.odk_key FROM household.b_member WHERE household.b_member.odk_key = NEW.odk_key); RAISE NOTICE 'test1'; IF (duplicate is not null) THEN RAISE NOTICE 'duplicate found so no data added'; RETURN NULL; ELSE RAISE NOTICE 'no duplicate found, entering data'; NEW.hh_id := (select household.a_household.id from household.a_household where household.a_household.odk_key = NEW.odk_parentkey); RAISE NOTICE 'NEW.hh_id %', NEW.hh_id; RETURN NEW; END IF; ELSE RAISE NOTICE 'Error'; RETURN NEW; END IF; ELSE RETURN NEW; END IF; END;$BODY$ The data in the table was entered without issue, all columns hold data of the correct datatype. When I try to update a value in the table (again with the correct data type) I receive the following error message:
ERROR: invalid input syntax for integer: "uuid:a89f5277-f057-4e46-9908-29c46419ff43/Household_composition-Household_Member[1]" This is a long key that comes from a piece of software. But it is not an integer and the column that it currently exists in is a text column. When disabling the trigger function, there is no issue making the update.
Would anyone be able to advise?
Becky
أكثر...
CREATE OR REPLACE FUNCTION household.before_b_member() RETURNS trigger AS $BODY$DECLARE duplicate integer; BEGIN RAISE NOTICE 'trigger start'; IF TG_OP = 'INSERT' or TG_OP = 'UPDATE' THEN IF (NEW.odk_key is not null) THEN RAISE NOTICE 'checking for duplicates'; duplicate := (SELECT household.b_member.odk_key FROM household.b_member WHERE household.b_member.odk_key = NEW.odk_key); RAISE NOTICE 'test1'; IF (duplicate is not null) THEN RAISE NOTICE 'duplicate found so no data added'; RETURN NULL; ELSE RAISE NOTICE 'no duplicate found, entering data'; NEW.hh_id := (select household.a_household.id from household.a_household where household.a_household.odk_key = NEW.odk_parentkey); RAISE NOTICE 'NEW.hh_id %', NEW.hh_id; RETURN NEW; END IF; ELSE RAISE NOTICE 'Error'; RETURN NEW; END IF; ELSE RETURN NEW; END IF; END;$BODY$ The data in the table was entered without issue, all columns hold data of the correct datatype. When I try to update a value in the table (again with the correct data type) I receive the following error message:
ERROR: invalid input syntax for integer: "uuid:a89f5277-f057-4e46-9908-29c46419ff43/Household_composition-Household_Member[1]" This is a long key that comes from a piece of software. But it is not an integer and the column that it currently exists in is a text column. When disabling the trigger function, there is no issue making the update.
Would anyone be able to advise?
Becky
أكثر...