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

Question

I want to CREATE CAST a suitable function to convert 'character varying' to 'integer'. Can anyone suggest a function? Everything I try fails.

You should fix your SQL to not rely in implict casts. Do not rely on implict casts in your statements, never. — user330315
– user330315, Commented Jan 21, 2011 at 15:54
I agree. However I cannot I'm using a off-the-shelf piece of software. — Simon
– Simon, Commented Jan 21, 2011 at 15:59

Peter Eisentraut · Accepted Answer · 2011-01-21 19:30:04Z

29

Use this:

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

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

answered Jan 21, 2011 at 19:30

Peter Eisentraut

37.1k12 gold badges91 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christian Over a year ago

More than 3k views/sp/queries to review&fix implicit casts... then you came with this f**** easy workaround. It'll get me so much more time. Thank you, job saver! (I'm wondering why postgres developers didn't put a parameter in postgresql.conf for this...)

Darren · Accepted Answer · 2012-05-04 14:56:58Z

1

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;

edited May 4, 2012 at 14:56

Darren

71.2k24 gold badges141 silver badges146 bronze badges

answered Sep 10, 2011 at 5:48

Michael Blood

1,26710 silver badges11 bronze badges

1 Comment

johnmartirano Over a year ago

This function was helpful for me to cast varchar fields as a regular DB user issuing queries rather than casting the type itself.

score 0 · Accepted Answer · 2011-01-21 17:05:32Z

0

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

edited Jan 21, 2011 at 17:05

answered Jan 21, 2011 at 16:00

user330315

4 Comments

Simon Over a year ago

Yeah. I found that but it's missing int4(varchar)

user330315 Over a year ago

It contains a cast for integer which is the same as int4 if I'm not mistaken.

Simon Over a year ago

Forgive me if I'm being stupid but none of the functions return an integer. I expect to do CREATE CAST (varchar as integer)...

user330315 Over a year ago

Forgive me! I was stupid. I thought you wanted the 8.3 behaviour back (int to varchar).

Oscar Raig Colon · Accepted Answer · 2012-05-15 16:02:25Z

0

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

answered May 15, 2012 at 16:02

Oscar Raig Colon

1,34012 silver badges15 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related