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