From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Phil Sorber <phil(at)omniti(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Fwd: PATCH: psql boolean display |
Date: | 2012-09-02 16:47:12 |
Message-ID: | CAFj8pRBUPmtJWPECk--yKC499Z_9e6Vps-jizwkWerps391W6Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-09-02 17:13:52 | Re: Yet another failure mode in pg_upgrade |
Previous Message | Pavel Stehule | 2012-09-02 16:30:17 | Re: Fwd: PATCH: psql boolean display |