Hi,
I have the following problem writing a function that checks if some
value exist.
I have a table "tKeyTable" that contains a column"key_no" which is a
VARCHAR.
The table "tPerson" has a column "position" which is also a VARCHAR. The
column "titel" is an INTEGER.
The function detects if the user inputs an invalid "postion" value but
ignores an invalid "titel" value. It seems the problem is different
datatype of "key_no" and "titel".
May anyone tell me how to rewrite the function that it works properly?
It would help me to know how change the INTEGER of new.titel into a string.
Greetings,
Andreas
CREATE FUNCTION fct_checkRefPerson() RETURNS opaque AS
'DECLARE
keytable record;
BEGIN
-- If there is no matching ID in tKeytable, raise an exception.
-- Position
SELECT INTO keytable *
FROM tKeyTable
WHERE key_no = new.position AND category=''position'';
IF NOT FOUND THEN
RAISE EXCEPTION ''...'';
RETURN old;
END IF;
-- Titel
SELECT INTO keytable *
FROM tKeyTable
WHERE key_no = new.titel AND category=''titel'';
IF NOT FOUND THEN
RAISE EXCEPTION ''...'';
RETURN old;
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql';
