Thread: 'true'::TEXT::BOOLEAN

'true'::TEXT::BOOLEAN

From
Markus Bertheau ☭
Date:
Hi,

What's the type I need to convert text to before I can convert it to
boolean?

Markus
-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN

From
Achilleus Mantzios
Date:
O Markus Bertheau β^Ψ­ έγραψε στις Jun 3, 2005 :

> Hi,
> 
> What's the type I need to convert text to before I can convert it to
> boolean?

just 't' will suffice.

> 
> Markus
> 

-- 
-Achilleus



Re: 'true'::TEXT::BOOLEAN

From
Markus Bertheau ☭
Date:
В Птн, 03/06/2005 в 14:20 +0300, Achilleus Mantzios пишет:
> O Markus Bertheau β^Ψ­ έγραψε στις Jun 3, 2005 :
> 
> > Hi,
> > 
> > What's the type I need to convert text to before I can convert it to
> > boolean?
> 
> just 't' will suffice.

Well, that's not my question. I have a plpgsql function like that:

CREATE FUNCTION object_new(class TEXT, properties TEXT[])

which I call like

object_new('Car', ARRAY['color', 'red', 'new', 'true'])

That means set color to red and new to true. In the function I need to
call

object_set_boolean_property(object_id INT, property_name TEXT,
property_value BOOLEAN)

And I can't call it with a TEXT variable, because casting from TEXT to
BOOLEAN isn't possible.

Markus
-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN

From
Achilleus Mantzios
Date:
O Markus Bertheau β^Ψ­ έγραψε στις Jun 3, 2005 :

> Π’ ΠŸΡ‚Π½, 03/06/2005 Π² 14:20 +0300, Achilleus Mantzios ΠΏΠΈΡˆΠ΅Ρ‚:
> > O Markus Bertheau Ξ²^Ψ­ έγραψΡ στις Jun 3, 2005 :
> > 
> > > Hi,
> > > 
> > > What's the type I need to convert text to before I can convert it to
> > > boolean?
> > 
> > just 't' will suffice.
> 
> Well, that's not my question. I have a plpgsql function like that:
> 
> CREATE FUNCTION object_new(class TEXT, properties TEXT[])
> 
> which I call like
> 
> object_new('Car', ARRAY['color', 'red', 'new', 'true'])
> 
> That means set color to red and new to true. In the function I need to
> call
> 
> object_set_boolean_property(object_id INT, property_name TEXT,
> property_value BOOLEAN)
> 
> And I can't call it with a TEXT variable, because casting from TEXT to
> BOOLEAN isn't possible.

Then use the 
case when ... then ... when ... then ... else ... end
construct, e.g.
case when mytext='true' then 't'::boolean else 'f'::boolean end

Hmm, why dont you leave it as 'true' or 'false' without any castings.

> 
> Markus
> 

-- 
-Achilleus



Re: 'true'::TEXT::BOOLEAN

From
Markus Bertheau ☭
Date:
В Птн, 03/06/2005 в 14:45 +0300, Achilleus Mantzios пишет:

> Then use the 
> case when ... then ... when ... then ... else ... end
> construct, e.g.
> case when mytext='true' then 't'::boolean else 'f'::boolean end

Because I don't want to reimplement postgres' boolean parsing.

> Hmm, why dont you leave it as 'true' or 'false' without any castings.

Because then pg doesn't find the function because it looks for one with
a text argument.

Markus

-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN

From
Michael Glaesemann
Date:
On Jun 3, 2005, at 8:52 PM, Markus Bertheau ☭ wrote:

> And I can't call it with a TEXT variable, because casting from TEXT to
> BOOLEAN isn't possible.


I'd be surprised if there weren't a some way to coerce the cast from
text to boolean, but you might want to just make a simple convenience
function in the interim:

test=# create or replace function text2bool (text)    returns boolean language sql as $$
select case    when lower($1) = 'true'        then true    else false    end;    $$;
CREATE FUNCTION

test=# select text2bool('true');
text2bool
-----------
t
(1 row)

test=# select text2bool('false');
text2bool
-----------
f
(1 row)


Just an idea.

Michael Glaesemann
grzm myrealbox com



Re: 'true'::TEXT::BOOLEAN

From
Markus Bertheau ☭
Date:
This also bypasses the built in postgresql boolean literal parsing.

I think casting from text to boolean should be possible, and use the
same algorithm that's used when casting from "unknown" to boolean.

Markus

В Птн, 03/06/2005 в 21:14 +0900, Michael Glaesemann пишет:
> On Jun 3, 2005, at 8:52 PM, Markus Bertheau ☭ wrote:
> 
> > And I can't call it with a TEXT variable, because casting from TEXT to
> > BOOLEAN isn't possible.
> 
> 
> I'd be surprised if there weren't a some way to coerce the cast from  
> text to boolean, but you might want to just make a simple convenience  
> function in the interim:
> 
> test=# create or replace function text2bool (text)
>      returns boolean language sql as $$
> select case
>      when lower($1) = 'true'
>          then true
>      else false
>      end;
>      $$;
> CREATE FUNCTION
> 
> test=# select text2bool('true');
> text2bool
> -----------
> t
> (1 row)
> 
> test=# select text2bool('false');
> text2bool
> -----------
> f
> (1 row)
> 
> 
> Just an idea.
> 
> Michael Glaesemann
> grzm myrealbox com
-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN

From
Michael Glaesemann
Date:
On Jun 3, 2005, at 9:23 PM, Markus Bertheau ☭ wrote:

> This also bypasses the built in postgresql boolean literal parsing.
>
> I think casting from text to boolean should be possible, and use the
> same algorithm that's used when casting from "unknown" to boolean.

Actually, looking at the system tables, I don't think it is. There
don't appear to be any casts to (or from) boolean. I may be looking
at it wrong, but that's how it appears to me. Corrections, anyone?

select type_source.typname as source, type_target.typname as target
from pg_cast
join pg_type type_source on (castsource = type_source.oid)
join pg_type type_target on (casttarget = type_target.oid)
where type_target.typname = 'bool'    or type_source.typname = 'bool'
order by type_source.typname;
 source | target
--------+--------
(0 rows)

This is in v8.0.3

Michael Glaesemann
grzm myrealbox com

PS. Please don't top post.



Re: 'true'::TEXT::BOOLEAN

From
Achilleus Mantzios
Date:
O Michael Glaesemann έγραψε στις Jun 3, 2005 :

> 
> On Jun 3, 2005, at 9:23 PM, Markus Bertheau ☭ wrote:
> 
> > This also bypasses the built in postgresql boolean literal parsing.
> >
> > I think casting from text to boolean should be possible, and use the
> > same algorithm that's used when casting from "unknown" to boolean.
> 
> Actually, looking at the system tables, I don't think it is. There  
> don't appear to be any casts to (or from) boolean. I may be looking  
> at it wrong, but that's how it appears to me. Corrections, anyone?
> 
> select type_source.typname as source, type_target.typname as target
> from pg_cast
> join pg_type type_source on (castsource = type_source.oid)
> join pg_type type_target on (casttarget = type_target.oid)
> where type_target.typname = 'bool'
>      or type_source.typname = 'bool'
> order by type_source.typname;
> 
>   source | target
> --------+--------
> (0 rows)
> 
> This is in v8.0.3

Also according to the docs:
http://www.postgresql.org/docs/current/static/datatype-boolean.html

"Tip: Values of the boolean type cannot be cast directly to other types 
(e.g., CAST (boolval AS integer) does not work). This can be accomplished 
using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE 
'value if false' END."

I suppose the reverse must be true also.

> 
> Michael Glaesemann
> grzm myrealbox com
> 
> PS. Please don't top post.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
-Achilleus



Re: 'true'::TEXT::BOOLEAN

From
Markus Bertheau ☭
Date:
В Птн, 03/06/2005 в 15:46 +0300, Achilleus Mantzios пишет:

> Also according to the docs:
> http://www.postgresql.org/docs/current/static/datatype-boolean.html
> 
> "Tip: Values of the boolean type cannot be cast directly to other types 
> (e.g., CAST (boolval AS integer) does not work). This can be accomplished 
> using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE 
> 'value if false' END."

Ah, that works. Thanks very much.

Markus
-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN doesn't work

From
Markus Bertheau ☭
Date:
В Птн, 03/06/2005 в 15:07 +0200, Markus Bertheau ☭ пишет:
> В Птн, 03/06/2005 в 15:46 +0300, Achilleus Mantzios пишет:
> 
> > Also according to the docs:
> > http://www.postgresql.org/docs/current/static/datatype-boolean.html
> > 
> > "Tip: Values of the boolean type cannot be cast directly to other types 
> > (e.g., CAST (boolval AS integer) does not work). This can be accomplished 
> > using the CASE expression: CASE WHEN boolval THEN 'value if true' ELSE 
> > 'value if false' END."
> 
> Ah, that works. Thanks very much.

Correcting myself, that doesn't work. It says something along the lines
of case expression must be of type boolean.

Markus
-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN

From
Tom Lane
Date:
Markus Bertheau ☭ <twanger@bluetwanger.de> writes:
> Well, that's not my question. I have a plpgsql function like that:
> ...
> And I can't call it with a TEXT variable, because casting from TEXT to
> BOOLEAN isn't possible.

In plpgsql it is: just assign the text value to a boolean variable.
plpgsql's notions of type safety are pretty lax ;-)
        regards, tom lane


Re: 'true'::TEXT::BOOLEAN

From
Bruno Wolff III
Date:
On Fri, Jun 03, 2005 at 14:23:37 +0200, Markus Bertheau ??? <twanger@bluetwanger.de> wrote:
> This also bypasses the built in postgresql boolean literal parsing.
> 
> I think casting from text to boolean should be possible, and use the
> same algorithm that's used when casting from "unknown" to boolean.

You probably want boolin.

area=> select boolin('0'), boolin('f'), boolin('false');boolin | boolin | boolin
--------+--------+--------f      | f      | f
(1 row)

Note that the function will error out if the argument is garbage.


Re: 'true'::TEXT::BOOLEAN

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
>   Markus Bertheau ??? <twanger@bluetwanger.de> wrote:
>> I think casting from text to boolean should be possible, and use the
>> same algorithm that's used when casting from "unknown" to boolean.

> You probably want boolin.

That won't actually work either, because boolin wants cstring:

egression=# select boolin('f'::text);
ERROR:  function boolin(text) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

You can get it to work like this, if you're determined:

regression=# select boolin(textout('f'::text));boolin
--------f
(1 row)

and of course

regression=# select textin(boolout(true));textin
--------t
(1 row)

There's been discussion of allowing all datatypes to be explicitly
casted to or from text by generating conversions like these
automatically.  But I'm not sure if everyone's convinced it's a good
idea or not.  You'd also have to argue about whether varchar should
be included in the special dispensation ...
        regards, tom lane


Re: 'true'::TEXT::BOOLEAN

From
Markus Bertheau ☭
Date:
В Птн, 03/06/2005 в 11:28 -0400, Tom Lane пишет:

> There's been discussion of allowing all datatypes to be explicitly
> casted to or from text by generating conversions like these
> automatically.  But I'm not sure if everyone's convinced it's a good
> idea or not. 

I certainly consider the way you proposed in the other mail a
workaround. What are the counter arguments?

Markus
-- 
Markus Bertheau ☭ <twanger@bluetwanger.de>



Re: 'true'::TEXT::BOOLEAN

From
Bruno Wolff III
Date:
On Fri, Jun 03, 2005 at 11:28:02 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> That won't actually work either, because boolin wants cstring:

Thanks for pointing that out. I was actually surprised to see my test work,
since I knew boolin expected cstring. I forgot that by not providing a type
strings get treated specially.
> There's been discussion of allowing all datatypes to be explicitly
> casted to or from text by generating conversions like these
> automatically.  But I'm not sure if everyone's convinced it's a good
> idea or not.  You'd also have to argue about whether varchar should
> be included in the special dispensation ...

I don't remember ever running accross documentation on how to solve this
problem. Even something noting that most of the type input and output
functions are named typein and typeout and that to convert to text you can
use textin(typeout(typevalue)) and to convert from text you can use
typein(textout(text)) would probably be good enough.