Re: Difference for Binary format vs Text format for client-server communication

On Sun, Jul 26, 2020 at 1:49 AM Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 2020-07-16 18:52, Andy Fan wrote:
> > The reason I ask this is because I have a task to make numeric output
> > similar to oracle.
> >
> > Oracle:
> >
> > SQL> select 2 / 1.0 from dual;
> >
> > 2/1.0
> > ----------
> > 2
> >
> > PG:
> >
> > postgres=# select 2 / 1.0;
> > ?column?
> > --------------------
> > 2.0000000000000000
> > (1 row)
> >
> > If the user uses text format, I can just hack some numeric_out function,
> > but if they
> > use binary format, looks I have to change the driver they used for it.
> > Am I
> > understand it correctly?
>
> I think what you should be looking at is why the numeric division
> function produces that scale and possibly make changes there.

Thanks, I think you are talking about the select_div_scale function, which
is
called before the real division task in div_var. so it will be hard to hack
at that part. Beside that, oracle returns the zero-trim version no matter
if division
is involved(I forgot to mention at the first).

At last, I just hacked the numeric_out function, then it works like Oracle
now.
However it just works in text format. I tried JDBC, and it uses text
format by
default. The solution is not good enough but it is ok for my purpose
currently.

IIUC, if a driver uses text protocol for a data type, then it works like
this: 1). server
gets a value in binary format. 2). server convert it to string and send it
via network,
3). client gets the string. 4). client converts the string to a given data
type. looks it is much
more complex than binary protocol. then why text protocol is chosen by
default.

> By the
> time the type's output or send function is invoked, that's already
> decided. The output/send functions are not the place to make scale or
> other semantic adjustments.
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

--
Best Regards
Andy Fan