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-02T22:50:08Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

I wrote:
> Attached is a draft patch that does this.  I'm fairly pleased with it,
> but there are some loose ends as described below.  As the patch stands,
> it reverse-lists all our special-format function call syntaxes
> *except* EXTRACT.  I left that out since I think we want to apply the
> reverse-listing change when we add the numeric-output extraction
> functions, as I said upthread.

> The main thing that's incomplete here is that the switch on function
> OID fails to cover some cases that ought to be covered, as a result
> of limitations of Gen_fmgrtab.pl:

Now that 8e1f37c07 fixed that, here's a complete version, with better
test coverage.  (I still think we might want to rewrite those SQL
functions as C, but that can be an independent project now.)

Remaining open issues:

* I notice that this will sometimes transform non-SQL-spec syntax
into SQL-spec, for example

# explain verbose select substring(now()::text, 'foo');
                     QUERY PLAN                      
-----------------------------------------------------
 Result  (cost=0.00..0.02 rows=1 width=32)
   Output: SUBSTRING((now())::text FROM 'foo'::text)
(2 rows)

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.
The reason this happens is that we've included expr_list as a variant of
substr_list, so that the func_expr_common_subexpr production has no idea
whether the argument list was really special syntax or not.  What I'm
inclined to do, but have not done yet, is to split that apart into
separate variants so that when the SQL-spec decoration is not used we
just generate a perfectly vanilla FuncCall.  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.

* Still waiting for comments on whether to rename CoercionForm.

			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.