From: | Rory Campbell-Lange <rory(at)campbell-lange(dot)net> |
---|---|
To: | 'Postgres general mailing list' <pgsql-general(at)postgresql(dot)org> |
Subject: | CAST to integer problem |
Date: | 2003-06-04 17:23:13 |
Message-ID: | 20030604172312.GB12022@campbell-lange.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)campbell-lange(dot)net>
<www.campbell-lange.net>
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Luc Lachance | 2003-06-04 17:40:00 | Re: CAST to integer problem |
Previous Message | Jonathan Gardner | 2003-06-04 15:30:29 | Re: newbie sql question... |