Re: TEXT / INTEGER problem in function

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
>
>