pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed'true'::TEXT::BOOLEAN
15+ messages / 5 participants
[nested] [flat]
* 'true'::TEXT::BOOLEAN
@ 2005-06-03 10:07 Markus Bertheau ☭ <twanger@bluetwanger.de>
0 siblings, 1 reply; 15+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-03 10:07 UTC (permalink / raw)
To: pgsql-sql
Hi,
What's the type I need to convert text to before I can convert it to
boolean?
Markus
--
Markus Bertheau ☭ <twanger@bluetwanger.de>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 11:20 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Markus Bertheau ☭ <twanger@bluetwanger.de>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2005-06-03 11:20 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 11:45 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Markus Bertheau ☭ <twanger@bluetwanger.de>
2 siblings, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2005-06-03 11:45 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 11:52 Markus Bertheau ☭ <twanger@bluetwanger.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 3 replies; 15+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-03 11:52 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
В Птн, 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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 12:03 Markus Bertheau ☭ <twanger@bluetwanger.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-03 12:03 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
В Птн, 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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 12:14 Michael Glaesemann <grzm@myrealbox.com>
parent: Markus Bertheau ☭ <twanger@bluetwanger.de>
2 siblings, 1 reply; 15+ messages in thread
From: Michael Glaesemann @ 2005-06-03 12:14 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 12:23 Markus Bertheau ☭ <twanger@bluetwanger.de>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 2 replies; 15+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-03 12:23 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: pgsql-sql
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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 12:28 Michael Glaesemann <grzm@myrealbox.com>
parent: Markus Bertheau ☭ <twanger@bluetwanger.de>
1 sibling, 1 reply; 15+ messages in thread
From: Michael Glaesemann @ 2005-06-03 12:28 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 12:46 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Michael Glaesemann <grzm@myrealbox.com>
0 siblings, 1 reply; 15+ messages in thread
From: Achilleus Mantzios @ 2005-06-03 12:46 UTC (permalink / raw)
To: Michael Glaesemann <grzm@myrealbox.com>; +Cc: Markus Bertheau ☭ <twanger@bluetwanger.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 13:07 Markus Bertheau ☭ <twanger@bluetwanger.de>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 0 replies; 15+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-03 13:07 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
В Птн, 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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 13:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Markus Bertheau ☭ <twanger@bluetwanger.de>
2 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2005-06-03 13:47 UTC (permalink / raw)
To: Markus Bertheau ☭ <twanger@bluetwanger.de>; +Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>; pgsql-sql
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= <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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 15:11 Bruno Wolff III <bruno@wolff.to>
parent: Markus Bertheau ☭ <twanger@bluetwanger.de>
1 sibling, 1 reply; 15+ messages in thread
From: Bruno Wolff III @ 2005-06-03 15:11 UTC (permalink / raw)
To: Markus Bertheau ??? <twanger@bluetwanger.de>; +Cc: Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 15:28 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 15+ messages in thread
From: Tom Lane @ 2005-06-03 15:28 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: Markus Bertheau ??? <twanger@bluetwanger.de>; Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 15:36 Markus Bertheau ☭ <twanger@bluetwanger.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 15+ messages in thread
From: Markus Bertheau ☭ @ 2005-06-03 15:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruno Wolff III <bruno@wolff.to>; Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
В Птн, 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>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: 'true'::TEXT::BOOLEAN
@ 2005-06-03 15:36 Bruno Wolff III <bruno@wolff.to>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 15+ messages in thread
From: Bruno Wolff III @ 2005-06-03 15:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Markus Bertheau ??? <twanger@bluetwanger.de>; Michael Glaesemann <grzm@myrealbox.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2005-06-03 15:36 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-03 10:07 'true'::TEXT::BOOLEAN Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-03 11:20 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-06-03 11:52 ` Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-03 11:45 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-06-03 12:03 ` Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-03 12:14 ` Michael Glaesemann <grzm@myrealbox.com>
2005-06-03 12:23 ` Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-03 12:28 ` Michael Glaesemann <grzm@myrealbox.com>
2005-06-03 12:46 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-06-03 13:07 ` Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-03 15:11 ` Bruno Wolff III <bruno@wolff.to>
2005-06-03 15:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-03 15:36 ` Markus Bertheau ☭ <twanger@bluetwanger.de>
2005-06-03 15:36 ` Bruno Wolff III <bruno@wolff.to>
2005-06-03 13:47 ` Tom Lane <tgl@sss.pgh.pa.us>
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