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