CODEWITHSUNDEEP
postgresql
Simon
Simon

Reputation: 5247

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.

Upvotes: 13

Views: 60304

Answers (4)

Oscar Raig Colon
Oscar Raig Colon

Reputation: 1340

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

Upvotes: 0

Michael Blood
Michael Blood

Reputation: 1267

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;

Upvotes: 1

Peter Eisentraut
Peter Eisentraut

Reputation: 36759

Use this:

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

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

Upvotes: 29

user330315
user330315

Reputation:

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);

Upvotes: 0

Related Questions