Thread: CAST to integer problem

CAST to integer problem

From
Rory Campbell-Lange
Date:
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>

Re: CAST to integer problem

From
Jean-Luc Lachance
Date:
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