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>