Re: CAST to integer problem

try int4( boardname) instead of CAST(boardname AS INTEGER)

and also, I prefer to do

if boardname ~ '^[0-9][0-9]*$' then ... to detect if it is a number.

That way you can weed out any non numeric character.

JLL





Rory Campbell-Lange wrote:
> 
> I have a function whose second parameter is defined as a varchar, but
> could be an integer. I test to see if it has a character in it,
> otherwise I try and do a cast to an integer. It isn't working. I'd be
> grateful for some tips.
> 
> Rory
> 
> --- select output ---------------------------------------------------
> 
> [boardname := 'henners']
> temporary=> select test ( 6, 'henners', 'new description');
>  test
> ------
>     1
> (1 row)
> 
> [boardname := '8']
> temporary=> select test ( 6, '8', 'new description');
>     WARNING:  Error occurred while executing PL/pgSQL function test
>     WARNING:  line 47 at assignment
>     ERROR:  Cannot cast type character varying to integer
> 
> [boardname := 8]
> temporary=> select test ( 6, 8, 'new description');
>     ERROR:  Function test(integer, integer, "unknown") does not exist
>             Unable to identify a function that satisfies the given argument types
>             You may need to add explicit typecasts
> 
> --- function definition (truncated) ---------------------------------
> 
> CREATE OR REPLACE FUNCTION test
>         (integer, varchar, varchar) RETURNS INTEGER
>     AS '
> DECLARE
>         creatorid        ALIAS for $1;
>         boardname        ALIAS for $2;
>         description      ALIAS for $3;
>         recone           RECORD;
>         boardid INTEGER  ;
> BEGIN
>         IF boardname ~* ''[a-z]'' THEN
>         -- find board identity number from select into recone
>         -- <snip>
>                 boardid := recone.n_id;
>         ELSE
>                 boardid := CAST(boardname AS INTEGER); -- <--- not working
>         -- do some more stuf
>         END IF;
> 
>         RETURN 1;
> 
> END;'
>     LANGUAGE plpgsql;
> 
> --
> Rory Campbell-Lange
> <rory@campbell-lange.net>
> <www.campbell-lange.net>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster