From: Marco Manfredini <mldb@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Conver bool to text
Date: Fri, 11 Mar 2005 23:12:28 +0100
User-Agent: KMail/1.6.2
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <200503112312.28266.mldb@gmx.net>


 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