Обсуждение: 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

<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