pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Conver bool to text
2+ messages / 2 participants
[nested] [flat]

* Conver bool to text
@ 2005-03-11 16:57  Edmund Bacon <ebacon@onesystem.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Edmund Bacon @ 2005-03-11 16:57 UTC (permalink / raw)
  To: pgsql-sql

Is there a way to convert a boolean value to text and/or vice versa?

You can
  select 'true'::boolean,
which *seems* to convert a text string to boolean, but
  select 'true'::text::boolean
earns the message
  ERROR:  cannot cast type text to boolean

You can't go the other way either
 SELECT true::text
 ERROR:  cannot cast type boolean to text

and to_char() doesn't know about booleans.

It's not that difficult to write a fuction to convert boolean to text, 
but I'm wondering if there's already something that does this?

-- 
Edmund Bacon <ebacon@onesystem.com>




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Conver bool to text
@ 2005-03-11 22:12  Marco Manfredini <mldb@gmx.net>
  0 siblings, 0 replies; 2+ messages in thread

From: Marco Manfredini @ 2005-03-11 22:12 UTC (permalink / raw)
  To: pgsql-sql




 Edmund Bacon wrote:
 
 > Is there a way to convert a boolean value to text and/or vice versa?
 >
 [cause it doesn't work as expected]
 > 
 > It's not that difficult to write a fuction to convert boolean to text,
 > but I'm wondering if there's already something that does this?
 
 You can help yourself by misusing the $type_in and $type_out functions: 
 
 create or replace function bool2text(bool) returns text as $$
  select textin(boolout($1)); 
 $$ language sql;  
 
 create cast (bool as text) with function bool2text(bool); 
 
 This is somewhat generic. Since the name of the *in and *out function
 can be read from pg_type, the conversion function can even be generated
 automatic: 
 /*
  warning: conversion via text representation isn't always right.
 */ 
 create or replace function make_conversion_function(s text, d text)
 returns void as 
 $$
  declare 
   tin text; 
   tout text; 
   xp text; 
  begin
  select typinput into tin from pg_catalog.pg_type where typname=d; 
  select typoutput into tout from pg_catalog.pg_type where typname=s; 
  xp:='create or replace function as_' || d || '(' || s || ') returns '
 || d || ' as $BODY$ select ' || tin || '(' || tout || '($1)) $BODY$
 language sql;'; 
  execute xp; 
  /* create cast analogue..*/
  return; 
  end; 
 $$ language plpgsql; 
 select make_conversion_function('text','bool');
 select make_conversion_function('bool','text');
 select as_bool('true'),as_text(true); 
 
 --- 
 Marco




^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2005-03-11 22:12 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-11 16:57 Conver bool to text Edmund Bacon <ebacon@onesystem.com>
2005-03-11 22:12 Re: Conver bool to text Marco Manfredini <mldb@gmx.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox