Re: Fwd: PATCH: psql boolean display - Mailing list pgsql-hackers

2012/9/2 Tom Lane <tgl@sss.pgh.pa.us>:
> I wrote:
>> Phil Sorber <phil@omniti.com> writes:
>>> What my patch was intended to do was let the end user set boolean
>>> output to any arbitrary values. While foo/bar is pretty useless, it
>>> was meant to reinforce that it was capable of any arbitrary value. I
>>> can think of a decent list of other output an end user might want,
>>> such as:
>
>>> true/false
>>> yes/no
>>> y/n
>>> on/off
>>> 1/0
>>> enabled/disabled
>
>>> Plus the different capitalized forms.
>
>> I can readily see that people might want boolean columns displayed in
>> such ways in custom applications.  I'm less convinced that there is much
>> use for it in psql, though.
>
> BTW, another point that your list brings to mind is that somebody who
> wants something like this would very possibly want different displays
> for different columns.  The proposed feature, being one-size-fits-all
> for "boolean", couldn't handle that.
>

I proposed just more cleaner and more conventional boolean output in
psql - nothing more. We can write formatting functions, CASE, we can
use enums.


> What would make a lot more sense in my mind would be to label columns
> *in the database* to show how they ought to be displayed.
>
> One conceivable method is to make a collection of domains over bool,
> and drive the display off the particular domain used.  However we lack
> some infrastructure that would be needed for this (in particular, I'm
> pretty sure the PQftype data delivered to the client identifies the
> underlying type and not the domain).
>
> Another approach is to make a collection of enum types, in which case
> you don't need any client-side support at all.  I experimented with
> this method a bit, and it seems workable:
>
> regression=# create type mybool as enum ('no', 'yes');
> CREATE TYPE
> regression=# create function bool(mybool) returns bool as
> $$ select $1 = 'yes'::mybool $$ language sql immutable;
> CREATE FUNCTION
> regression=# create cast (mybool as bool) with function bool(mybool) as assignment;
> CREATE CAST
> regression=# create table mb(f1 mybool);
> CREATE TABLE
> regression=# insert into mb values('no'),('yes');
> INSERT 0 2
> regression=# select * from mb where f1;
>  f1
> -----
>  yes
> (1 row)
>
> regression=# select * from mb where f1 = 'yes';
>  f1
> -----
>  yes
> (1 row)
>
> I tried making the cast be implicit, but that caused problems with
> ambiguous operators, so assignment seems to be the best you can do here.
>
> A variant of this is to build casts in the other direction
> (bool::mybool), declare columns in the database as regular bool,
> and apply the casts in queries when you want columns displayed in a
> particular way.  This might be the best solution if the desired
> display is at all context-dependent.

When I worked on PSM I required possibility to simple specification
expected datatype out of SQL statement - some like enhancing
parametrised queries - with fourth parameter - expected types.

Then somebody can set expected type for some column simply - out of
query - and transformation can be fast. It should be used for
unsupported date formats and similar tasks.

Regards

Pavel

>
>                         regards, tom lane