Обсуждение: Casting to varchar
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 to varchar? -- Scott Ribe scott_ribe@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice
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