Thread

1. CAST to integer problem

   Rory Campbell-Lange <rory@campbell-lange.net> — 2003-06-04T17:23:13Z

   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>
   
   

2. Re: CAST to integer problem

   Jean-Luc Lachance <jllachan@nsd.ca> — 2003-06-04T17:40:00Z

   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