Thread

1. Fwd: PATCH: psql boolean display

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-09-02T05:13:19Z

   ---------- Forwarded message ----------
   From: Pavel Stehule <pavel.stehule@gmail.com>
   Date: 2012/9/1
   Subject: PATCH: psql boolean display
   To: Phil Sorber <phil@omniti.com>
   
   
   Hello
   
   I am looking to your patch:
   
   I have only one note. I don't think so using any text for values
   "true" and "false" is good idea. I don't see a sense of any special
   texts like "foo", "bar" from your example.
   
   More strict design is better - I wrote simple modification - it is
   based on psql psets - and it add new configuration settings "boolstyle
   [char|word]". A advantage of this design is consistency  and possible
   autocomplete support.
   
   Regards
   
   Pavel
   
   
   
   postgres=> select true, false;
    bool │ bool
   ──────┼──────
    t    │ f
   (1 row)
   
   postgres=> \pset boolstyle word
   Bool output style is word.
   postgres=> select true, false;
    bool │ bool
   ──────┼───────
    true │ false
   (1 row)
   

2. Re: Fwd: PATCH: psql boolean display

   Phil Sorber <phil@omniti.com> — 2012-09-02T14:51:53Z

   On Sun, Sep 2, 2012 at 1:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
   > ---------- Forwarded message ----------
   > From: Pavel Stehule <pavel.stehule@gmail.com>
   > Date: 2012/9/1
   > Subject: PATCH: psql boolean display
   > To: Phil Sorber <phil@omniti.com>
   >
   >
   > Hello
   >
   > I am looking to your patch:
   >
   > I have only one note. I don't think so using any text for values
   > "true" and "false" is good idea. I don't see a sense of any special
   > texts like "foo", "bar" from your example.
   >
   > More strict design is better - I wrote simple modification - it is
   > based on psql psets - and it add new configuration settings "boolstyle
   > [char|word]". A advantage of this design is consistency  and possible
   > autocomplete support.
   >
   > Regards
   >
   > Pavel
   >
   >
   >
   > postgres=> select true, false;
   >  bool │ bool
   > ──────┼──────
   >  t    │ f
   > (1 row)
   >
   > postgres=> \pset boolstyle word
   > Bool output style is word.
   > postgres=> select true, false;
   >  bool │ bool
   > ──────┼───────
   >  true │ false
   > (1 row)
   >
   >
   > --
   > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/mailpref/pgsql-hackers
   >
   
   What you are proposing sounds like it would be better suited to a
   server side GUC. Based on the discussion in the thread that said
   true/false was the SQL standard and we were doing it incorrectly as
   t/f but could not change for legacy reasons. We could even change the
   default but give users a way to revert to the old behavior. Similar to
   the bytea_output GUC. Maybe add the GUC for 9.3 but not change the
   default behavior until 10.0.
   
   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.
   
   
   

3. Re: Fwd: PATCH: psql boolean display

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-09-02T15:05:46Z

   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.  In the big scheme of things, psql is a
   rather low-level tool, designed for DBAs and SQL programmers.  I'd get
   quite upset if psql failed to tell me the truth about what was in a
   table I was looking at --- and a feature like this comes pretty close
   to not telling the truth, especially if it kicks in on a column I
   wasn't expecting it to.
   
   On the whole I think this sort of substitution belongs in a
   user-written-application layer of software, not in any of the tools
   we supply.
   
   			regards, tom lane
   
   
   

4. Re: Fwd: PATCH: psql boolean display

   Tom Lane <tgl@sss.pgh.pa.us> — 2012-09-02T15:45:46Z

   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.
   
   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.
   
   			regards, tom lane
   
   
   

5. Re: Fwd: PATCH: psql boolean display

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-09-02T16:30:17Z

   2012/9/2 Phil Sorber <phil@omniti.com>:
   > On Sun, Sep 2, 2012 at 1:13 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
   >> ---------- Forwarded message ----------
   >> From: Pavel Stehule <pavel.stehule@gmail.com>
   >> Date: 2012/9/1
   >> Subject: PATCH: psql boolean display
   >> To: Phil Sorber <phil@omniti.com>
   >>
   >>
   >> Hello
   >>
   >> I am looking to your patch:
   >>
   >> I have only one note. I don't think so using any text for values
   >> "true" and "false" is good idea. I don't see a sense of any special
   >> texts like "foo", "bar" from your example.
   >>
   >> More strict design is better - I wrote simple modification - it is
   >> based on psql psets - and it add new configuration settings "boolstyle
   >> [char|word]". A advantage of this design is consistency  and possible
   >> autocomplete support.
   >>
   >> Regards
   >>
   >> Pavel
   >>
   >>
   >>
   >> postgres=> select true, false;
   >>  bool │ bool
   >> ──────┼──────
   >>  t    │ f
   >> (1 row)
   >>
   >> postgres=> \pset boolstyle word
   >> Bool output style is word.
   >> postgres=> select true, false;
   >>  bool │ bool
   >> ──────┼───────
   >>  true │ false
   >> (1 row)
   >>
   >>
   >> --
   >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
   >> To make changes to your subscription:
   >> http://www.postgresql.org/mailpref/pgsql-hackers
   >>
   >
   > What you are proposing sounds like it would be better suited to a
   > server side GUC. Based on the discussion in the thread that said
   > true/false was the SQL standard and we were doing it incorrectly as
   > t/f but could not change for legacy reasons. We could even change the
   > default but give users a way to revert to the old behavior. Similar to
   > the bytea_output GUC. Maybe add the GUC for 9.3 but not change the
   > default behavior until 10.0.
   >
   > 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.
   
   If you have these different requests, then you can use enums - or you
   can use own formatting function. There is relative strong
   recommendation don't use implicit formatting based on database
   configuration from application and inside application use explicit
   formatting anywhere. I don't thing so using GUC for boolean datatype
   is good idea.
   
   Using just chars 't' and 'f' is unlucky design, that must be respected
   due compatibility reasons. You don't need to solve it usually, because
   transformation from chars to words can do application or database
   driver - so I understand this as client issue - psql issue in this
   case. And I really don't see any sense for unlimited bool output - in
   simple tool like psql. It can be nice to fix issue with chars, because
   chars are not too pronounced, but we don't need to supply enums.
   
   Regards
   
   Pavel
   
   
   

6. Re: Fwd: PATCH: psql boolean display

   Pavel Stehule <pavel.stehule@gmail.com> — 2012-09-02T16:47:12Z

   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
   
   
   

7. Re: Fwd: PATCH: psql boolean display

   Heikki Linnakangas <hlinnakangas@vmware.com> — 2012-09-20T07:54:25Z

   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