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

It doesn't look like this patch is going anywhere. I agree with Tom's 
comments that we need to think how this works for all datatypes, not 
just booleans. And a simple substitution of values isn't enough; an 
application might want to output all integers in hex, for example. A 
custom domain in the server is one way to implement that, or you can 
check the datatype in the application and act accordingly. It doesn't 
belong in psql, so I'll mark this as rejected in the commitfest app.

On 02.09.2012 19:47, Pavel Stehule wrote:
> 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
>


-- 
- Heikki