Обсуждение: PATCH: psql boolean display
Hello all, I am providing a patch to allow you to change the output of a boolean value in psql much like you can do with NULL. A client requested this feature and we thought it may appeal to someone else in the community. The patch includes updated docs and a regression test. The code changes themselves are pretty simple and straightforward. Example from the regression test: SELECT true, false; bool | bool ------+------ t | f (1 row) \pset booltrue 'foo' \pset boolfalse 'bar' SELECT true, false; bool | bool ------+------ foo | bar (1 row) \pset booltrue 't' \pset boolfalse 'f' SELECT true, false; bool | bool ------+------ t | f (1 row) As always, comments welcome. Thanks.
Вложения
On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> wrote: > I am providing a patch to allow you to change the output of a boolean > value in psql much like you can do with NULL. A client requested this > feature and we thought it may appeal to someone else in the community. > > The patch includes updated docs and a regression test. The code > changes themselves are pretty simple and straightforward. > > Example from the regression test: > > SELECT true, false; > bool | bool > ------+------ > t | f > (1 row) > > \pset booltrue 'foo' > \pset boolfalse 'bar' > SELECT true, false; > bool | bool > ------+------ > foo | bar > (1 row) > > \pset booltrue 't' > \pset boolfalse 'f' > SELECT true, false; > bool | bool > ------+------ > t | f > (1 row) > > As always, comments welcome. Why not just do it in the SQL? SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2012/8/20 Robert Haas <robertmhaas@gmail.com>: > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> wrote: >> I am providing a patch to allow you to change the output of a boolean >> value in psql much like you can do with NULL. A client requested this >> feature and we thought it may appeal to someone else in the community. >> >> The patch includes updated docs and a regression test. The code >> changes themselves are pretty simple and straightforward. >> >> Example from the regression test: >> >> SELECT true, false; >> bool | bool >> ------+------ >> t | f >> (1 row) >> >> \pset booltrue 'foo' >> \pset boolfalse 'bar' >> SELECT true, false; >> bool | bool >> ------+------ >> foo | bar >> (1 row) >> >> \pset booltrue 't' >> \pset boolfalse 'f' >> SELECT true, false; >> bool | bool >> ------+------ >> t | f >> (1 row) >> >> As always, comments welcome. > > Why not just do it in the SQL? > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; I understand this motivation - although I was more happy with server side solution. Regards Pavel Stehule > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
<p>On Aug 20, 2012 5:11 PM, "Pavel Stehule" <<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>>wrote:<br /> ><br /> > 2012/8/20 Robert Haas <<ahref="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>:<br /> > > On Sun, Aug 19, 2012 at 12:02 PM,Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > >> I am providing a patchto allow you to change the output of a boolean<br /> > >> value in psql much like you can do with NULL. A clientrequested this<br /> > >> feature and we thought it may appeal to someone else in the community.<br /> >>><br /> > >> The patch includes updated docs and a regression test. The code<br /> > >> changesthemselves are pretty simple and straightforward.<br /> > >><br /> > >> Example from the regressiontest:<br /> > >><br /> > >> SELECT true, false;<br /> > >> bool | bool<br /> > >>------+------<br /> > >> t | f<br /> > >> (1 row)<br /> > >><br /> > >> \psetbooltrue 'foo'<br /> > >> \pset boolfalse 'bar'<br /> > >> SELECT true, false;<br /> > >> bool | bool<br /> > >> ------+------<br /> > >> foo | bar<br /> > >> (1 row)<br />> >><br /> > >> \pset booltrue 't'<br /> > >> \pset boolfalse 'f'<br /> > >> SELECTtrue, false;<br /> > >> bool | bool<br /> > >> ------+------<br /> > >> t | f<br />> >> (1 row)<br /> > >><br /> > >> As always, comments welcome.<br /> > ><br /> > >Why not just do it in the SQL?<br /> > ><br /> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END ASwhatever;<br /> ><br /> > I understand this motivation - although I was more happy with server<br /> > side solution.<br/> ><p>Was a server side implementation submitted before? I can change it, but I did it on the client sidelike the null display was done.<p>> Regards<br /> ><br /> > Pavel Stehule<br /> ><br /> > ><br /> >> --<br /> > > Robert Haas<br /> > > EnterpriseDB: <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/> > > The Enterprise PostgreSQL Company<br />> ><br /> > ><br /> > > --<br /> > > Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> > > To make changes to your subscription:<br/> > > <a href="http://www.postgresql.org/mailpref/pgsql-hackers">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/>
<p>On Aug 20, 2012 5:19 PM, "Phil Sorber" <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> ><br/> > On Aug 20, 2012 5:11 PM, "Pavel Stehule" <<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>>wrote:<br /> > ><br /> > > 2012/8/20 RobertHaas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>:<br /> > > > On Sun, Aug 19,2012 at 12:02 PM, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > > >>I am providing a patch to allow you to change the output of a boolean<br /> > > >> value in psql muchlike you can do with NULL. A client requested this<br /> > > >> feature and we thought it may appeal to someoneelse in the community.<br /> > > >><br /> > > >> The patch includes updated docs and a regressiontest. The code<br /> > > >> changes themselves are pretty simple and straightforward.<br /> > >>><br /> > > >> Example from the regression test:<br /> > > >><br /> > > >>SELECT true, false;<br /> > > >> bool | bool<br /> > > >> ------+------<br /> > >>> t | f<br /> > > >> (1 row)<br /> > > >><br /> > > >> \pset booltrue'foo'<br /> > > >> \pset boolfalse 'bar'<br /> > > >> SELECT true, false;<br /> > >>> bool | bool<br /> > > >> ------+------<br /> > > >> foo | bar<br /> > > >>(1 row)<br /> > > >><br /> > > >> \pset booltrue 't'<br /> > > >> \pset boolfalse'f'<br /> > > >> SELECT true, false;<br /> > > >> bool | bool<br /> > > >>------+------<br /> > > >> t | f<br /> > > >> (1 row)<br /> > > >><br />> > >> As always, comments welcome.<br /> > > ><br /> > > > Why not just do it in the SQL?<br/> > > ><br /> > > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;<br /> >><br /> > > I understand this motivation - although I was more happy with server<br /> > > side solution.<br/> > ><br /> ><br /> > Was a server side implementation submitted before? I can change it, but Idid it on the client side like the null display was done.<p>Or how about both?<p>><br /> > > Regards<br /> >><br /> > > Pavel Stehule<br /> > ><br /> > > ><br /> > > > --<br /> > > >Robert Haas<br /> > > > EnterpriseDB: <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/> > > > The Enterprise PostgreSQL Company<br/> > > ><br /> > > ><br /> > > > --<br /> > > > Sent via pgsql-hackers mailinglist (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> > > > To makechanges to your subscription:<br /> > > > <a href="http://www.postgresql.org/mailpref/pgsql-hackers">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/>
On 20 August 2012 22:10, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2012/8/20 Robert Haas <robertmhaas@gmail.com>: >> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> wrote: >>> I am providing a patch to allow you to change the output of a boolean >>> value in psql much like you can do with NULL. A client requested this >>> feature and we thought it may appeal to someone else in the community. >>> >>> The patch includes updated docs and a regression test. The code >>> changes themselves are pretty simple and straightforward. >>> >>> Example from the regression test: >>> >>> SELECT true, false; >>> bool | bool >>> ------+------ >>> t | f >>> (1 row) >>> >>> \pset booltrue 'foo' >>> \pset boolfalse 'bar' >>> SELECT true, false; >>> bool | bool >>> ------+------ >>> foo | bar >>> (1 row) >>> >>> \pset booltrue 't' >>> \pset boolfalse 'f' >>> SELECT true, false; >>> bool | bool >>> ------+------ >>> t | f >>> (1 row) >>> >>> As always, comments welcome. >> >> Why not just do it in the SQL? >> >> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; > > I understand this motivation - although I was more happy with server > side solution. Wouldn't a server-side solution risk breaking many things that depends on such a representation? You wouldn't be able to pick and choose what comes out of the server on a per-application basis unless you use cumbersome CASE clauses in every query that returns boolean data. It sounds like keeping it at the application level is the least disruptive, and there is a precedent for such changes, such as NULL representation. -- Thom
On 20 August 2012 22:31, Phil Sorber <phil@omniti.com> wrote: > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil@omniti.com> wrote: >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel.stehule@gmail.com> wrote: >> > >> > 2012/8/20 Robert Haas <robertmhaas@gmail.com>: >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> wrote: >> > >> I am providing a patch to allow you to change the output of a boolean >> > >> value in psql much like you can do with NULL. A client requested this >> > >> feature and we thought it may appeal to someone else in the >> > >> community. >> > >> >> > >> The patch includes updated docs and a regression test. The code >> > >> changes themselves are pretty simple and straightforward. >> > >> >> > >> Example from the regression test: >> > >> >> > >> SELECT true, false; >> > >> bool | bool >> > >> ------+------ >> > >> t | f >> > >> (1 row) >> > >> >> > >> \pset booltrue 'foo' >> > >> \pset boolfalse 'bar' >> > >> SELECT true, false; >> > >> bool | bool >> > >> ------+------ >> > >> foo | bar >> > >> (1 row) >> > >> >> > >> \pset booltrue 't' >> > >> \pset boolfalse 'f' >> > >> SELECT true, false; >> > >> bool | bool >> > >> ------+------ >> > >> t | f >> > >> (1 row) >> > >> >> > >> As always, comments welcome. >> > > >> > > Why not just do it in the SQL? >> > > >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; >> > >> > I understand this motivation - although I was more happy with server >> > side solution. >> > >> >> Was a server side implementation submitted before? I can change it, but I >> did it on the client side like the null display was done. > > Or how about both? Surely one would break the other? -- Thom
2012/8/20 Thom Brown <thom@linux.com>: > On 20 August 2012 22:10, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2012/8/20 Robert Haas <robertmhaas@gmail.com>: >>> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> wrote: >>>> I am providing a patch to allow you to change the output of a boolean >>>> value in psql much like you can do with NULL. A client requested this >>>> feature and we thought it may appeal to someone else in the community. >>>> >>>> The patch includes updated docs and a regression test. The code >>>> changes themselves are pretty simple and straightforward. >>>> >>>> Example from the regression test: >>>> >>>> SELECT true, false; >>>> bool | bool >>>> ------+------ >>>> t | f >>>> (1 row) >>>> >>>> \pset booltrue 'foo' >>>> \pset boolfalse 'bar' >>>> SELECT true, false; >>>> bool | bool >>>> ------+------ >>>> foo | bar >>>> (1 row) >>>> >>>> \pset booltrue 't' >>>> \pset boolfalse 'f' >>>> SELECT true, false; >>>> bool | bool >>>> ------+------ >>>> t | f >>>> (1 row) >>>> >>>> As always, comments welcome. >>> >>> Why not just do it in the SQL? >>> >>> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; >> >> I understand this motivation - although I was more happy with server >> side solution. > > Wouldn't a server-side solution risk breaking many things that depends > on such a representation? You wouldn't be able to pick and choose > what comes out of the server on a per-application basis unless you use > cumbersome CASE clauses in every query that returns boolean data. > > It sounds like keeping it at the application level is the least > disruptive, and there is a precedent for such changes, such as NULL > representation. It is similar to datetime formatting or to number formatting. Sometimes it is issue for some untyped languages because 't' or 'f' has not adequate sense - but it is unfriendly when somebody working with console. console based solution like proposed patch is probably the most less evil solution. Using 't' and 'f' was not a best idea. > > -- > Thom
<p><br /> On Aug 20, 2012 5:56 PM, "Thom Brown" <<a href="mailto:thom@linux.com">thom@linux.com</a>> wrote:<br /> ><br/> > On 20 August 2012 22:31, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br/> > > On Aug 20, 2012 5:19 PM, "Phil Sorber" <<a href="mailto:phil@omniti.com">phil@omniti.com</a>>wrote:<br /> > >><br /> > >> On Aug 20, 2012 5:11PM, "Pavel Stehule" <<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>> wrote:<br /> > >>><br /> > >> > 2012/8/20 Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>:<br/> > >> > > On Sun, Aug 19, 2012 at 12:02PM, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > >> > >>I am providing a patch to allow you to change the output of a boolean<br /> > >> > >> value inpsql much like you can do with NULL. A client requested this<br /> > >> > >> feature and we thought itmay appeal to someone else in the<br /> > >> > >> community.<br /> > >> > >><br />> >> > >> The patch includes updated docs and a regression test. The code<br /> > >> > >>changes themselves are pretty simple and straightforward.<br /> > >> > >><br /> > >> >>> Example from the regression test:<br /> > >> > >><br /> > >> > >> SELECTtrue, false;<br /> > >> > >> bool | bool<br /> > >> > >> ------+------<br /> >>> > >> t | f<br /> > >> > >> (1 row)<br /> > >> > >><br /> >>> > >> \pset booltrue 'foo'<br /> > >> > >> \pset boolfalse 'bar'<br /> > >>> >> SELECT true, false;<br /> > >> > >> bool | bool<br /> > >> > >>------+------<br /> > >> > >> foo | bar<br /> > >> > >> (1 row)<br /> >>> > >><br /> > >> > >> \pset booltrue 't'<br /> > >> > >> \pset boolfalse'f'<br /> > >> > >> SELECT true, false;<br /> > >> > >> bool | bool<br /> >>> > >> ------+------<br /> > >> > >> t | f<br /> > >> > >> (1row)<br /> > >> > >><br /> > >> > >> As always, comments welcome.<br /> > >>> ><br /> > >> > > Why not just do it in the SQL?<br /> > >> > ><br /> > >>> > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;<br /> > >> ><br /> > >>> I understand this motivation - although I was more happy with server<br /> > >> > side solution.<br/> > >> ><br /> > >><br /> > >> Was a server side implementation submitted before?I can change it, but I<br /> > >> did it on the client side like the null display was done.<br /> > ><br/> > > Or how about both?<br /> ><br /> > Surely one would break the other?<br /> ><p>If using both.<p>>--<br /> > Thom<br />
On 20 August 2012 23:06, Phil Sorber <phil@omniti.com> wrote: > > On Aug 20, 2012 5:56 PM, "Thom Brown" <thom@linux.com> wrote: >> >> On 20 August 2012 22:31, Phil Sorber <phil@omniti.com> wrote: >> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil@omniti.com> wrote: >> >> >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel.stehule@gmail.com> >> >> wrote: >> >> > >> >> > 2012/8/20 Robert Haas <robertmhaas@gmail.com>: >> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> >> >> > > wrote: >> >> > >> I am providing a patch to allow you to change the output of a >> >> > >> boolean >> >> > >> value in psql much like you can do with NULL. A client requested >> >> > >> this >> >> > >> feature and we thought it may appeal to someone else in the >> >> > >> community. >> >> > >> >> >> > >> The patch includes updated docs and a regression test. The code >> >> > >> changes themselves are pretty simple and straightforward. >> >> > >> >> >> > >> Example from the regression test: >> >> > >> >> >> > >> SELECT true, false; >> >> > >> bool | bool >> >> > >> ------+------ >> >> > >> t | f >> >> > >> (1 row) >> >> > >> >> >> > >> \pset booltrue 'foo' >> >> > >> \pset boolfalse 'bar' >> >> > >> SELECT true, false; >> >> > >> bool | bool >> >> > >> ------+------ >> >> > >> foo | bar >> >> > >> (1 row) >> >> > >> >> >> > >> \pset booltrue 't' >> >> > >> \pset boolfalse 'f' >> >> > >> SELECT true, false; >> >> > >> bool | bool >> >> > >> ------+------ >> >> > >> t | f >> >> > >> (1 row) >> >> > >> >> >> > >> As always, comments welcome. >> >> > > >> >> > > Why not just do it in the SQL? >> >> > > >> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; >> >> > >> >> > I understand this motivation - although I was more happy with server >> >> > side solution. >> >> > >> >> >> >> Was a server side implementation submitted before? I can change it, but >> >> I >> >> did it on the client side like the null display was done. >> > >> > Or how about both? >> >> Surely one would break the other? >> > > If using both. Yes. :) -- Thom
On Mon, Aug 20, 2012 at 5:54 PM, Thom Brown <thom@linux.com> wrote:
On occasions I have wanted psql to emit the full 'True'/'False' words instead of cryptic one-letter t/f, which can get lost on long rows that get wrapped around on screen. Writing long-winded CASE expressions to get the effect is too much for small ad-hoc queries.
I thought of inventing a data type whose out-function would emit these strings, and tack a ::mybool to the expression I want modified. But that would break the applications if somebody pasted the same query in an application (JDBC or some such that understands boolean) and expected a boolean data type instead of a text output of an expression.
Wouldn't a server-side solution risk breaking many things that dependsOn 20 August 2012 22:10, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2012/8/20 Robert Haas <robertmhaas@gmail.com>:
>> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> wrote:
>>> I am providing a patch to allow you to change the output of a boolean
>>> value in psql much like you can do with NULL. A client requested this
>>> feature and we thought it may appeal to someone else in the community.
>>>
>>> The patch includes updated docs and a regression test. The code
>>> changes themselves are pretty simple and straightforward.
>>>
>>> Example from the regression test:
>>>
>>> SELECT true, false;
>>> bool | bool
>>> ------+------
>>> t | f
>>> (1 row)
>>>
>>> \pset booltrue 'foo'
>>> \pset boolfalse 'bar'
>>> SELECT true, false;
>>> bool | bool
>>> ------+------
>>> foo | bar
>>> (1 row)
>>>
>>> \pset booltrue 't'
>>> \pset boolfalse 'f'
>>> SELECT true, false;
>>> bool | bool
>>> ------+------
>>> t | f
>>> (1 row)
>>>
>>> As always, comments welcome.
>>
>> Why not just do it in the SQL?
>>
>> SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;
>
> I understand this motivation - although I was more happy with server
> side solution.
on such a representation? You wouldn't be able to pick and choose
what comes out of the server on a per-application basis unless you use
cumbersome CASE clauses in every query that returns boolean data.
It sounds like keeping it at the application level is the least
disruptive, and there is a precedent for such changes, such as NULL
representation.
On occasions I have wanted psql to emit the full 'True'/'False' words instead of cryptic one-letter t/f, which can get lost on long rows that get wrapped around on screen. Writing long-winded CASE expressions to get the effect is too much for small ad-hoc queries.
I thought of inventing a data type whose out-function would emit these strings, and tack a ::mybool to the expression I want modified. But that would break the applications if somebody pasted the same query in an application (JDBC or some such that understands boolean) and expected a boolean data type instead of a text output of an expression.
I think there's a merit to psql supporting this feature, because psql is most commonly used for ad-hoc interactive use, and true/false is more human consumable than t/f (I have had a Java developer ask me what was that 't' value in the resultset in psql).
--
Gurjeet Singh
<p><br /> On Aug 20, 2012 6:08 PM, "Thom Brown" <<a href="mailto:thom@linux.com">thom@linux.com</a>> wrote:<br /> ><br/> > On 20 August 2012 23:06, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br/> > ><br /> > > On Aug 20, 2012 5:56 PM, "Thom Brown" <<a href="mailto:thom@linux.com">thom@linux.com</a>>wrote:<br /> > >><br /> > >> On 20 August 2012 22:31,Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > >> > On Aug 20,2012 5:19 PM, "Phil Sorber" <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > >> >><br/> > >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>><br/> > >> >> wrote:<br /> > >>>> ><br /> > >> >> > 2012/8/20 Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>:<br/> > >> >> > > On Sun, Aug 19,2012 at 12:02 PM, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>><br /> > >> >>> > wrote:<br /> > >> >> > >> I am providing a patch to allow you to change the outputof a<br /> > >> >> > >> boolean<br /> > >> >> > >> value in psql muchlike you can do with NULL. A client requested<br /> > >> >> > >> this<br /> > >> >>> >> feature and we thought it may appeal to someone else in the<br /> > >> >> > >>community.<br /> > >> >> > >><br /> > >> >> > >> The patch includesupdated docs and a regression test. The code<br /> > >> >> > >> changes themselves are prettysimple and straightforward.<br /> > >> >> > >><br /> > >> >> > >> Examplefrom the regression test:<br /> > >> >> > >><br /> > >> >> > >> SELECTtrue, false;<br /> > >> >> > >> bool | bool<br /> > >> >> > >> ------+------<br/> > >> >> > >> t | f<br /> > >> >> > >> (1 row)<br />> >> >> > >><br /> > >> >> > >> \pset booltrue 'foo'<br /> > >>>> > >> \pset boolfalse 'bar'<br /> > >> >> > >> SELECT true, false;<br />> >> >> > >> bool | bool<br /> > >> >> > >> ------+------<br /> > >>>> > >> foo | bar<br /> > >> >> > >> (1 row)<br /> > >> >>> >><br /> > >> >> > >> \pset booltrue 't'<br /> > >> >> > >>\pset boolfalse 'f'<br /> > >> >> > >> SELECT true, false;<br /> > >> >> >>> bool | bool<br /> > >> >> > >> ------+------<br /> > >> >> > >> t | f<br /> > >> >> > >> (1 row)<br /> > >> >> > >><br /> >>> >> > >> As always, comments welcome.<br /> > >> >> > ><br /> > >>>> > > Why not just do it in the SQL?<br /> > >> >> > ><br /> > >> >>> > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;<br /> > >> >> ><br />> >> >> > I understand this motivation - although I was more happy with server<br /> > >> >>> side solution.<br /> > >> >> ><br /> > >> >><br /> > >> >> Wasa server side implementation submitted before? I can change it, but<br /> > >> >> I<br /> > >>>> did it on the client side like the null display was done.<br /> > >> ><br /> > >> >Or how about both?<br /> > >><br /> > >> Surely one would break the other?<br /> > >><br />> ><br /> > > If using both.<br /> ><br /> > Yes. :)<p>Really server would override client.<p>><br/> > --<br /> > Thom<br />
Gurjeet Singh <singh.gurjeet@gmail.com> wrote: > On occasions I have wanted psql to emit the full 'True'/'False' > words instead of cryptic one-letter t/f, which can get lost on > long rows that get wrapped around on screen. Writing long-winded > CASE expressions to get the effect is too much for small ad-hoc > queries. > > I thought of inventing a data type whose out-function would emit > these strings, and tack a ::mybool to the expression I want > modified. But that would break the applications if somebody pasted > the same query in an application (JDBC or some such that > understands boolean) and expected a boolean data type instead of a > text output of an expression. The type itself does output true/false; it's just psql that uses t/f. test=# select 'true'::boolean::text;text ------true (1 row) test=# select 'false'::boolean::text;text -------false (1 row) That has always seemed quite odd (and occasionally inconvenient) to me. -Kevin
On 20 August 2012 23:16, Phil Sorber <phil@omniti.com> wrote: > > On Aug 20, 2012 6:08 PM, "Thom Brown" <thom@linux.com> wrote: >> >> On 20 August 2012 23:06, Phil Sorber <phil@omniti.com> wrote: >> > >> > On Aug 20, 2012 5:56 PM, "Thom Brown" <thom@linux.com> wrote: >> >> >> >> On 20 August 2012 22:31, Phil Sorber <phil@omniti.com> wrote: >> >> > On Aug 20, 2012 5:19 PM, "Phil Sorber" <phil@omniti.com> wrote: >> >> >> >> >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <pavel.stehule@gmail.com> >> >> >> wrote: >> >> >> > >> >> >> > 2012/8/20 Robert Haas <robertmhaas@gmail.com>: >> >> >> > > On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil@omniti.com> >> >> >> > > wrote: >> >> >> > >> I am providing a patch to allow you to change the output of a >> >> >> > >> boolean >> >> >> > >> value in psql much like you can do with NULL. A client >> >> >> > >> requested >> >> >> > >> this >> >> >> > >> feature and we thought it may appeal to someone else in the >> >> >> > >> community. >> >> >> > >> >> >> >> > >> The patch includes updated docs and a regression test. The code >> >> >> > >> changes themselves are pretty simple and straightforward. >> >> >> > >> >> >> >> > >> Example from the regression test: >> >> >> > >> >> >> >> > >> SELECT true, false; >> >> >> > >> bool | bool >> >> >> > >> ------+------ >> >> >> > >> t | f >> >> >> > >> (1 row) >> >> >> > >> >> >> >> > >> \pset booltrue 'foo' >> >> >> > >> \pset boolfalse 'bar' >> >> >> > >> SELECT true, false; >> >> >> > >> bool | bool >> >> >> > >> ------+------ >> >> >> > >> foo | bar >> >> >> > >> (1 row) >> >> >> > >> >> >> >> > >> \pset booltrue 't' >> >> >> > >> \pset boolfalse 'f' >> >> >> > >> SELECT true, false; >> >> >> > >> bool | bool >> >> >> > >> ------+------ >> >> >> > >> t | f >> >> >> > >> (1 row) >> >> >> > >> >> >> >> > >> As always, comments welcome. >> >> >> > > >> >> >> > > Why not just do it in the SQL? >> >> >> > > >> >> >> > > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever; >> >> >> > >> >> >> > I understand this motivation - although I was more happy with >> >> >> > server >> >> >> > side solution. >> >> >> > >> >> >> >> >> >> Was a server side implementation submitted before? I can change it, >> >> >> but >> >> >> I >> >> >> did it on the client side like the null display was done. >> >> > >> >> > Or how about both? >> >> >> >> Surely one would break the other? >> >> >> > >> > If using both. >> >> Yes. :) > > Really server would override client. Come to think of it, if the client could detect the server's bool out config, it could override the server in that instance since it would know what it was looking for, so perhaps they could coexist. -- Thom
<p><br /> On Aug 20, 2012 6:28 PM, "Kevin Grittner" <<a href="mailto:Kevin.Grittner@wicourts.gov">Kevin.Grittner@wicourts.gov</a>>wrote:<br /> ><br /> > Gurjeet Singh <<ahref="mailto:singh.gurjeet@gmail.com">singh.gurjeet@gmail.com</a>> wrote:<br /> ><br /> > > On occasionsI have wanted psql to emit the full 'True'/'False'<br /> > > words instead of cryptic one-letter t/f, whichcan get lost on<br /> > > long rows that get wrapped around on screen. Writing long-winded<br /> > > CASEexpressions to get the effect is too much for small ad-hoc<br /> > > queries.<br /> > ><br /> > > Ithought of inventing a data type whose out-function would emit<br /> > > these strings, and tack a ::mybool to theexpression I want<br /> > > modified. But that would break the applications if somebody pasted<br /> > > thesame query in an application (JDBC or some such that<br /> > > understands boolean) and expected a boolean datatype instead of a<br /> > > text output of an expression.<br /> ><br /> > The type itself does output true/false;it's just psql that uses<br /> > t/f.<br /> ><br /> > test=# select 'true'::boolean::text;<br /> > text<br /> > ------<br /> > true<br /> > (1 row)<br /> ><br /> > test=# select 'false'::boolean::text;<br/> > text<br /> > -------<br /> > false<br /> > (1 row)<br /> ><br /> > Thathas always seemed quite odd (and occasionally inconvenient) to<br /> > me.<p>I think that may be from the cast. Ididn't see any transformation in psql. Looked like it was raw output from the server.<p>><br /> > -Kevin<br />
<p><br /> On Aug 20, 2012 6:31 PM, "Thom Brown" <<a href="mailto:thom@linux.com">thom@linux.com</a>> wrote:<br /> ><br/> > On 20 August 2012 23:16, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br/> > ><br /> > > On Aug 20, 2012 6:08 PM, "Thom Brown" <<a href="mailto:thom@linux.com">thom@linux.com</a>>wrote:<br /> > >><br /> > >> On 20 August 2012 23:06,Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > >> ><br /> >>> > On Aug 20, 2012 5:56 PM, "Thom Brown" <<a href="mailto:thom@linux.com">thom@linux.com</a>> wrote:<br/> > >> >><br /> > >> >> On 20 August 2012 22:31, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>>wrote:<br /> > >> >> > On Aug 20, 2012 5:19 PM, "PhilSorber" <<a href="mailto:phil@omniti.com">phil@omniti.com</a>> wrote:<br /> > >> >> >><br/> > >> >> >> On Aug 20, 2012 5:11 PM, "Pavel Stehule" <<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>><br/> > >> >> >> wrote:<br /> >>> >> >> ><br /> > >> >> >> > 2012/8/20 Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>:<br/> > >> >> >> > > On Sun,Aug 19, 2012 at 12:02 PM, Phil Sorber <<a href="mailto:phil@omniti.com">phil@omniti.com</a>><br /> > >>>> >> > > wrote:<br /> > >> >> >> > >> I am providing a patch toallow you to change the output of a<br /> > >> >> >> > >> boolean<br /> > >> >>>> > >> value in psql much like you can do with NULL. A client<br /> > >> >> >>> >> requested<br /> > >> >> >> > >> this<br /> > >> >> >>> >> feature and we thought it may appeal to someone else in the<br /> > >> >> >> >>> community.<br /> > >> >> >> > >><br /> > >> >> >> > >>The patch includes updated docs and a regression test. The code<br /> > >> >> >> > >>changes themselves are pretty simple and straightforward.<br /> > >> >> >> > >><br/> > >> >> >> > >> Example from the regression test:<br /> > >> >>>> > >><br /> > >> >> >> > >> SELECT true, false;<br /> > >>>> >> > >> bool | bool<br /> > >> >> >> > >> ------+------<br/> > >> >> >> > >> t | f<br /> > >> >> >> > >>(1 row)<br /> > >> >> >> > >><br /> > >> >> >> > >> \psetbooltrue 'foo'<br /> > >> >> >> > >> \pset boolfalse 'bar'<br /> > >> >>>> > >> SELECT true, false;<br /> > >> >> >> > >> bool | bool<br />> >> >> >> > >> ------+------<br /> > >> >> >> > >> foo |bar<br /> > >> >> >> > >> (1 row)<br /> > >> >> >> > >><br/> > >> >> >> > >> \pset booltrue 't'<br /> > >> >> >> >>> \pset boolfalse 'f'<br /> > >> >> >> > >> SELECT true, false;<br /> > >>>> >> > >> bool | bool<br /> > >> >> >> > >> ------+------<br/> > >> >> >> > >> t | f<br /> > >> >> >> > >>(1 row)<br /> > >> >> >> > >><br /> > >> >> >> > >> Asalways, comments welcome.<br /> > >> >> >> > ><br /> > >> >> >> > >Why not just do it in the SQL?<br /> > >> >> >> > ><br /> > >> >> >>> > SELECT CASE WHEN whatever THEN 'foo' ELSE 'bar' END AS whatever;<br /> > >> >> >>><br /> > >> >> >> > I understand this motivation - although I was more happy with<br/> > >> >> >> > server<br /> > >> >> >> > side solution.<br /> >>> >> >> ><br /> > >> >> >><br /> > >> >> >> Was a serverside implementation submitted before? I can change it,<br /> > >> >> >> but<br /> > >>>> >> I<br /> > >> >> >> did it on the client side like the null display was done.<br/> > >> >> ><br /> > >> >> > Or how about both?<br /> > >> >><br/> > >> >> Surely one would break the other?<br /> > >> >><br /> > >> ><br/> > >> > If using both.<br /> > >><br /> > >> Yes. :)<br /> > ><br /> > >Really server would override client.<br /> ><br /> > Come to think of it, if the client could detect the server'sbool out<br /> > config, it could override the server in that instance since it would<br /> > know what itwas looking for, so perhaps they could coexist.<p>I think Pavel has a patch in that is meant to sync variables betweenclient and server. Perhaps we can use the same facility?<p>><br /> > --<br /> > Thom<br />
> On Aug 20, 2012 6:28 PM, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> > wrote: >> >> Gurjeet Singh <singh.gurjeet@gmail.com> wrote: >> >> > On occasions I have wanted psql to emit the full 'True'/'False' >> > words instead of cryptic one-letter t/f, which can get lost on >> > long rows that get wrapped around on screen. Writing long-winded >> > CASE expressions to get the effect is too much for small ad-hoc >> > queries. >> > >> > I thought of inventing a data type whose out-function would emit >> > these strings, and tack a ::mybool to the expression I want >> > modified. But that would break the applications if somebody pasted >> > the same query in an application (JDBC or some such that >> > understands boolean) and expected a boolean data type instead of a >> > text output of an expression. >> >> The type itself does output true/false; it's just psql that uses >> t/f. >> >> test=# select 'true'::boolean::text; >> text >> ------ >> true >> (1 row) >> >> test=# select 'false'::boolean::text; >> text >> ------- >> false >> (1 row) >> >> That has always seemed quite odd (and occasionally inconvenient) to >> me. > > I think that may be from the cast. I didn't see any transformation in psql. > Looked like it was raw output from the server. Right. "t", "f" are generated in backend. See boolout() in backend/utils/adt/bool.c for more details. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > The type itself does output true/false; it's just psql that uses > t/f. No, 't'/'f' is what boolout() returns. The 'true'/'false' results from casting bool to text are intentionally different --- IIRC, Peter E. argued successfully that this cast behavior is required by SQL spec. But we'd already been returning 't'/'f' to applications for far too many years to change it. (And that argument has not gotten any weaker since then. Keep in mind that Postgres was returning 't'/'f' for bool years before the SQL spec even had a boolean type.) If we're going to do something like this at all, I agree that psql is the place to do it, not the server. But my beef with this patch is that it's thinking too small --- why would bool be the only type that somebody would want to editorialize on the display of? I'd rather see some general "substitute this for that in display of columns of type X" feature. regards, tom lane
On Mon, Aug 20, 2012 at 7:19 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> The type itself does output true/false; it's just psql that uses >> t/f. > > No, 't'/'f' is what boolout() returns. The 'true'/'false' results from > casting bool to text are intentionally different --- IIRC, Peter E. > argued successfully that this cast behavior is required by SQL spec. > But we'd already been returning 't'/'f' to applications for far too many > years to change it. (And that argument has not gotten any weaker since > then. Keep in mind that Postgres was returning 't'/'f' for bool years > before the SQL spec even had a boolean type.) > > If we're going to do something like this at all, I agree that psql is > the place to do it, not the server. But my beef with this patch is that > it's thinking too small --- why would bool be the only type that > somebody would want to editorialize on the display of? I'd rather see > some general "substitute this for that in display of columns of type X" > feature. > > regards, tom lane Sorry, was on my phone before and couldn't type the response I wanted to. I like where your head is with the more general case, and I tried to think along those lines too, but I could not come up with a grand unifying way to do even null and boolean together, never mind other ways that I hadn't even thought about. The boolean case is a single datatype where the null case crosses all nullable types. With null you only have to handle one case, with boolean, you have two. What I settled upon was the simplest way I could think of with the most flexibility. In my opinion, the utility of this patch outweigh's the niche implementation. I'd welcome other approaches that covered this in a more generic way, I just can't think of any that aren't overly complex.
2012/8/21 Tom Lane <tgl@sss.pgh.pa.us>: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> The type itself does output true/false; it's just psql that uses >> t/f. > > No, 't'/'f' is what boolout() returns. The 'true'/'false' results from > casting bool to text are intentionally different --- IIRC, Peter E. > argued successfully that this cast behavior is required by SQL spec. > But we'd already been returning 't'/'f' to applications for far too many > years to change it. (And that argument has not gotten any weaker since > then. Keep in mind that Postgres was returning 't'/'f' for bool years > before the SQL spec even had a boolean type.) > > If we're going to do something like this at all, I agree that psql is > the place to do it, not the server. But my beef with this patch is that > it's thinking too small --- why would bool be the only type that > somebody would want to editorialize on the display of? I'd rather see > some general "substitute this for that in display of columns of type X" > feature. > can you explain your idea, please? - I can't to imagine any general solution for other types than "enum" like types (without significant enhancing of psql scripting possibilities) Regards Pavel > regards, tom lane