Casting to varchar

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;

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

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?

Sure, see CREATE CAST.

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

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.

Sure, see CREATE CAST.

Too simple ;-) I was expecting to have to dig into data type definitions...

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On May 4, 2007, at 15:34 , Scott Ribe wrote:

Are there any other standard types that can't be cast
to varchar?

You already got an answer to the first part of your question, but I
thought you might be interested in the second as well. Here's what I
did:

SELECT DISTINCT cast_from
FROM pg_cast c
NATURAL JOIN (
SELECT oid as castsource, typname as cast_from
FROM pg_type
) s
WHERE NOT EXISTS (
SELECT 1
FROM pg_cast i
NATURAL JOIN (
SELECT oid as casttarget, typname as cast_target
FROM pg_type
) t
WHERE cast_target = 'text'
AND i.castsource = c.castsource
)
ORDER BY cast_from;

cast_from
--------------
abstime
bit
bool
box
circle
lseg
path
polygon
regclass
regoper
regoperator
regproc
regprocedure
regtype
reltime
text
varbit
(17 rows)

I don't know which of those you'd consider standard, but I believe
that's a complete list from HEAD of a few minutes ago.

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:

On May 4, 2007, at 15:34 , Scott Ribe wrote:

Are there any other standard types that can't be cast
to varchar?

You already got an answer to the first part of your question, but I
thought you might be interested in the second as well.

Note that there's a proposal to allow explicit casts to text from any
type (by invoking the appropriate I/O function behind the scenes) and
I imagine we'd allow casts to varchar as well. Not sure if this will
happen for 8.3, although it still could.

regards, tom lane

You already got an answer to the first part of your question, but I
thought you might be interested in the second as well. Here's what I
did:

Thanks. That's very helpful, to see a good example of using the built-in
catalog data.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice