Re: Casting to varchar

Raymond O'Donnell wrote:
> On 04/05/2007 21:34, Scott Ribe wrote:
>
> >Just discovered (the hard way) that casting a boolean column ::varchar
> >doesn't work. I assume I can add a function somewhere that will define a
> >default cast for this? Are there any other standard types that can't be
> >cast
>
> I just use something like this:
>
> create or replace function bool2str(TheValue boolean)
> returns varchar as
> $$
> begin
> if TheValue then
> return 'true';
> else
> return 'false';
> end if;
> end;
> $$
> language plpgsql stable;

To complete the example,

alvherre=# create cast (boolean as varchar) with function bool2str(bool);
CREATE CAST

alvherre=# select 't'::boolean::varchar;
varchar
---------
true
(1 fila)

Though I'd mark the function immutable rather than stable.

alvherre=# select 'f'::boolean::varchar;
varchar
---------
false
(1 fila)

alvherre=# select '0'::boolean::varchar;
varchar
---------
false
(1 fila)

alvherre=# select '123'::boolean::varchar;
ERROR: invalid input syntax for type boolean: "123"

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.