Thread: TEXT / INTEGER problem in function

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

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