Horton

Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: Vik Fearing <vik@postgresfriends.org>, Thomas Munro <thomas.munro@gmail.com>, Petr Fedorov <petr.fedorov@phystech.edu>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2020-11-04T00:22:14Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

I wrote:
> * I notice that this will sometimes transform non-SQL-spec syntax
> into SQL-spec, for example ...
> I'm not sure that that satisfies the POLA.  This particular case is
> especially not great, because this is really textregexsubstr() which
> is *not* SQL compatible, so the display is more than a bit misleading.

Actually, the problem there is that I made ruleutils.c willing to
reverse-list textregexsubstr() in SQL syntax, which it really shouldn't
since there is no such function per SQL.  So deleting that "case" value
is enough to fix most of the problem.  Still:

> ... In fact, I'd sort of argue
> that we should not force the function to be sought in pg_catalog in such
> a case either.  The comments in substr_list claim that we're trying to
> allow extension functions named substring(), but using SystemFuncName is
> 100% hostile to that.

... this seems like a reasonable argument.  However, in the attached
I only did that for SUBSTRING and OVERLAY.  I had thought of doing
it for POSITION and TRIM, but both of those are weird enough that
allowing a "normal function call" seems error-prone.  For example,
the fact that TRIM(expr_list) works out as a call to btrim() is a mess,
but I don't think we can change it.  (But of course you can still call a
user-defined trim() function if you double-quote the function name.)

I did get rid of the empty variant for position_list, which AFAICS
has no value except adding confusion: there are no zero-argument
functions named "position" in pg_catalog.

I feel like this is committable at this point --- any objections?

			regards, tom lane

Commits

  1. Fix inconsistent equalfuncs.c behavior for FuncCall.funcformat.

  2. Doc: fix discussion of how to get real Julian Dates.

  3. Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation.

  4. Change return type of EXTRACT to numeric

  5. Improve our ability to regurgitate SQL-syntax function calls.

  6. Add more tests for EXTRACT of date type

  7. Expose internal function for converting int64 to numeric

  8. Change floating-point output format for improved performance.