Postgresql. CREATE CAST 'character varying' to 'integer'

I want to CREATE CAST a suitable function to 开发者_开发技巧convert 'character varying' to 'integer'. Can anyone suggest a function? Everything I try fails.

Use this:

CREATE CAST (varchar AS integer) WITH INOUT [AS IMPLICIT];

It uses the input/output functions of the data types involved.

The function above works if you update your SQL to do an explicit cast. However, if you add 'as implicit' to the end of your "create cast" statement, Postgres will be able to automatically figure out what you are trying to do when you compare an integer with a varchar that can be converted to an integer.

Here is my updated statement that seemed to work "implicitly":

CREATE FUNCTION toint(varchar) 
  RETURNS integer 
  STRICT IMMUTABLE LANGUAGE SQL AS 
'SELECT cast($1 as integer);';

CREATE CAST (varchar AS integer) WITH FUNCTION toint(varchar) as Implicit;

I assume you want to get back the behaviour of Postgres 8.3 regarding implicit casting.

See this blog entry for examples:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

Oh: and bug that vendor of the software to fix the broken SQL ;)

Edit

This should do it:

CREATE FUNCTION toint(varchar) 
  RETURNS integer 
  STRICT IMMUTABLE LANGUAGE SQL AS 
'SELECT cast($1 as integer);';

CREATE CAST (varchar AS integer) WITH FUNCTION toint(varchar);

I got the same error. I have a COLUMN

code

declared as type character varying(20) and a local variable

l_code

declared as type int.

I solved replacing in the procedure

SELECT 
...
WHERE
code = l_code AND
..

with

code = cast( l_code as character varying)   AND