Thread: 'true'::TEXT::BOOLEAN
Hi, What's the type I need to convert text to before I can convert it to boolean? Markus -- Markus Bertheau ☭ <twanger@bluetwanger.de>
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
В Птн, 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>
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
В Птн, 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>
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
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>
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.
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
В Птн, 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>
В Птн, 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>
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
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.
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
В Птн, 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>
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.