Re: Fwd: PATCH: psql boolean display

2012/9/2 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> I wrote:
>> Phil Sorber <phil(at)omniti(dot)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