I found the error: the column 'titel' has already a default. If I remove
the row with default from tKeyTable the function works as expected.
Sorry!
Andreas Schlegel wrote:
> 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';
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>