Re: PATCH: psql boolean display

On Mon, Aug 20, 2012 at 5:54 PM, Thom Brown <thom(at)linux(dot)com> wrote:

> On 20 August 2012 22:10, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> > 2012/8/20 Robert Haas <robertmhaas(at)gmail(dot)com>:
> >> On Sun, Aug 19, 2012 at 12:02 PM, Phil Sorber <phil(at)omniti(dot)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.

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