To: David Stanaway <david@stanaway.net>
Cc: Scott Royston <scroyston@mac.com>, pgsql-sql@postgresql.org
Subject: Re: can't cast varchar as integer? 
In-reply-to: <1021412859.992.2468.camel@ciderbox> 
References: <4EE21904-676C-11D6-BF4D-000393577144@mac.com>
	<1021412859.992.2468.camel@ciderbox>
Comments: In-reply-to David Stanaway <david@stanaway.net>
	message dated "14 May 2002 16:47:39 -0500"
Date: Tue, 14 May 2002 19:29:10 -0400
Message-ID: <1600.1021418950@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

David Stanaway <david@stanaway.net> writes:
>> LEDEV=3D# select cast(foo as integer) from test;
>> ERROR:  Cannot cast type 'character varying' to 'integer'

> scratch=3D# select foo::text::integer from test;
> [works]

> scratch=3D# select int4(foo) from test;
> [works]

For reasons that I don't entirely recall at the moment (but they seemed
good to the pghackers list at the time), cast notations only work if
there is a cast function *exactly* matching the requested cast.
On the other hand, the functional form is laxer because there's an
allowed step of implicit coercion before the function call.

In the case at hand, there's a text->int4 cast function (look in
pg_proc, you'll see int4(text)) but there's no int4(varchar) function.
Also, varchar can be cast to text implicitly --- this is actually
a "binary equivalent" cast requiring no run-time effort.  So
	select foo::text::integer from test;
works: it's a binary-equivalent cast from varchar to text, followed
by application of int4(text).  And
	 select int4(foo) from test;
works because the same function is found and implicit coercion of
its argument to text succeeds.  But
	select cast(foo as integer) from test;
doesn't work because there's no declared function int4(varchar).

There's probably not any good reason why there's not int4(varchar),
just that no one got around to making one.

			regards, tom lane