Thread: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch
From
Peter Eisentraut
Date:
On 2019-12-02 23:52, Thomas Munro wrote: >> I'm not an expert in floating point math but hopefully it means that no >> type change is required - double precision can handle it. > Me neither, but the SQL standard requires us to use an exact numeric > type, so it's wrong on that level by definition. I looked into this (changing the return types of date_part()/extract() from float8 to numeric). One problem (other than perhaps performance, tbd.) is that this would no longer allow processing infinite timestamps, since numeric does not support infinity. It could be argued that running extract() on infinite timestamps isn't very useful, but it's something to consider explicitly. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> One problem (other than perhaps performance, tbd.) is that this would no
> longer allow processing infinite timestamps, since numeric does not
> support infinity. It could be argued that running extract() on infinite
> timestamps isn't very useful, but it's something to consider explicitly.
I wonder if it's time to fix that, ie introduce +-Infinity into numeric.c.
This isn't the first time we've seen issues with numeric not being a
superset of float, and it won't be the last.
At first glance there's no free bits in the on-disk format for numeric,
but we could do something by defining the low-order bits of the header
word for a NaN to distinguish between real NaN and +/- infinity.
It looks like those bits should reliably be zero right now.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch
From
Vik Fearing
Date:
On 5/25/20 3:28 PM, Peter Eisentraut wrote: > On 2019-12-02 23:52, Thomas Munro wrote: >>> I'm not an expert in floating point math but hopefully it means that no >>> type change is required - double precision can handle it. >> Me neither, but the SQL standard requires us to use an exact numeric >> type, so it's wrong on that level by definition. > > I looked into this (changing the return types of date_part()/extract() > from float8 to numeric). I think what would be better is to have a specific date_part function for each part and have extract translate to the appropriate one. This is particularly interesting for epoch but it would also allow us to return the correct type mandated by the spec. (I would also accept a specific date_part per return type instead of per part, that would probably even be better.) -- Vik Fearing
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Vik Fearing <vik@postgresfriends.org> writes:
> On 5/25/20 3:28 PM, Peter Eisentraut wrote:
>> I looked into this (changing the return types of date_part()/extract()
>> from float8 to numeric).
> I think what would be better is to have a specific date_part function
> for each part and have extract translate to the appropriate one.
Doesn't really work for upwards compatibility with existing views,
which will have calls to date_part(text, ...) embedded in them.
Actually, now that I think about it, changing the result type of
date_part() is likely to be problematic anyway for such cases.
It's not going to be good if pg_upgrade's dump/restore of a view
results in a new output column type; especially if it's a
materialized view.
So maybe what we'd have to do is leave date_part() alone for
legacy compatibility, and invent new functions that the extract()
syntax would now be translated to. While at it, maybe we could
fix things so that the syntax reverse-lists the same way instead
of injecting Postgres-isms...
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost whenextracting epoch
From
Vik Fearing
Date:
On 5/25/20 6:40 PM, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> On 5/25/20 3:28 PM, Peter Eisentraut wrote:
>>> I looked into this (changing the return types of date_part()/extract()
>>> from float8 to numeric).
>
>> I think what would be better is to have a specific date_part function
>> for each part and have extract translate to the appropriate one.
>
> Doesn't really work for upwards compatibility with existing views,
> which will have calls to date_part(text, ...) embedded in them.
>
> Actually, now that I think about it, changing the result type of
> date_part() is likely to be problematic anyway for such cases.
> It's not going to be good if pg_upgrade's dump/restore of a view
> results in a new output column type; especially if it's a
> materialized view.
>
> So maybe what we'd have to do is leave date_part() alone for
> legacy compatibility, and invent new functions that the extract()
> syntax would now be translated to.
I'm sorry, I wasn't clear. I was suggesting adding new functions while
also keeping the current generic function. So exactly what you say in
that last paragraph.
Although <extract expression> has a fixed list of constant parts,
date_part() allows the part to be variable. So we need to keep it
anyway for cases like this contrived example:
SELECT date_part(p, now())
FROM UNNEST(ARRAY['epoch', 'year', 'second']) AS u (p)
> While at it, maybe we could
> fix things so that the syntax reverse-lists the same way instead
> of injecting Postgres-isms...
I'm not sure what this means.
--
Vik Fearing
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Vik Fearing <vik@postgresfriends.org> writes:
> On 5/25/20 6:40 PM, Tom Lane wrote:
>> While at it, maybe we could
>> fix things so that the syntax reverse-lists the same way instead
>> of injecting Postgres-isms...
> I'm not sure what this means.
This:
regression=# create view myview as select extract(year from current_timestamp) as y;
CREATE VIEW
regression=# \d+ myview
...
View definition:
SELECT date_part('year'::text, CURRENT_TIMESTAMP) AS y;
What had been a 100% spec-compliant view definition is now quite
Postgres-specific. I fixed some similar problems in 0bb51aa96 (before
that, the CURRENT_TIMESTAMP part would've reverse-listed differently
too); but I didn't tackle EXTRACT(), SUBSTRING(), and other cases.
I'm not claiming that we really need to fix all of those. But if we are
going to pick nits about which data type EXTRACT() returns then I think
it's legit to worry about its reverse-list representation at the same
time ... especially if we must touch the grammar's translation anyway.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
I wrote:
> What had been a 100% spec-compliant view definition is now quite
> Postgres-specific. I fixed some similar problems in 0bb51aa96 (before
> that, the CURRENT_TIMESTAMP part would've reverse-listed differently
> too); but I didn't tackle EXTRACT(), SUBSTRING(), and other cases.
> I'm not claiming that we really need to fix all of those. But if we are
> going to pick nits about which data type EXTRACT() returns then I think
> it's legit to worry about its reverse-list representation at the same
> time ... especially if we must touch the grammar's translation anyway.
BTW, shortly after sending that I had an idea about how to do it without
adding a boatload of new parsetree infrastructure, which has been the
main reason why nobody has wanted to tackle it. The obvious way to do
this is to make a new kind of expression node, but that cascades into
lots and lots of places (see 0bb51aa96, plus the later commits that
fixed oversights in it :-(). It's a lot of work for a mostly-cosmetic
issue.
However: suppose that we continue to translate these things into FuncExpr
nodes, the same as always, but we add a new CoercionForm variant, say
COERCE_SQL_SYNTAX. 99% of the system ignores FuncExpr.funcformat,
and would continue to do so, but ruleutils.c would take it to mean
that (1) the call should be reverse-listed as some special SQL syntax
and (2) the funcid is one of a small set of built-in functions for
which ruleutils.c knows what to emit. (If it doesn't recognize the
funcid, it could either throw an error, or fall back to normal display
of the node.) For cases such as EXTRACT, this would also represent
a promise that specific arguments are Const nodes from which the
desired keyword can be extracted.
This is kind of an abuse of "CoercionForm", since that typedef name
implies that it only talks about how to handle cast cases, but
semantically it's always been a how-to-display-function-calls thing.
We could either hold our noses about that or rename the typedef.
If we went this way then we could easily clean up most of the other
weird-SQL-syntax function call cases, incrementally over time,
without a lot of additional work.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lostwhen extracting epoch
From
David Fetter
Date:
On Mon, May 25, 2020 at 09:43:32AM -0400, Tom Lane wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: > > One problem (other than perhaps performance, tbd.) is that this would no > > longer allow processing infinite timestamps, since numeric does not > > support infinity. It could be argued that running extract() on infinite > > timestamps isn't very useful, but it's something to consider explicitly. > > I wonder if it's time to fix that, ie introduce +-Infinity into numeric.c. > This isn't the first time we've seen issues with numeric not being a > superset of float, and it won't be the last. > > At first glance there's no free bits in the on-disk format for numeric, > but we could do something by defining the low-order bits of the header > word for a NaN to distinguish between real NaN and +/- infinity. > It looks like those bits should reliably be zero right now. +1 for adding +/- infinity to NUMERIC. Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 2020-05-25 15:28, Peter Eisentraut wrote:
> On 2019-12-02 23:52, Thomas Munro wrote:
>>> I'm not an expert in floating point math but hopefully it means that no
>>> type change is required - double precision can handle it.
>> Me neither, but the SQL standard requires us to use an exact numeric
>> type, so it's wrong on that level by definition.
>
> I looked into this (changing the return types of date_part()/extract()
> from float8 to numeric).
>
> One problem (other than perhaps performance, tbd.) is that this would no
> longer allow processing infinite timestamps, since numeric does not
> support infinity. It could be argued that running extract() on infinite
> timestamps isn't very useful, but it's something to consider explicitly.
Now that numeric supports infinity, here is a patch that changes the
return types of date_part() to numeric. It's not meant to be a final
version, but it is useful for discussing a few things.
The internal implementation could be made a bit more elegant if we had
variants of int4_numeric() and int8_numeric() that don't have to go
through fmgr. This would also help in other areas of the code. There
are probably also other ways in which the internals could be made more
compact; I just converted them fairly directly.
When extracting seconds or microseconds, I made it always produce 6 or 3
decimal places, even if they are zero. I don't know if we want that or
what behavior we want. That's what all the changes in the regression
tests are about. Everything else passes unchanged.
The 'julian' field is a bit of a mystery. First of all it's not
documented. The regression tests only test the rounded output, perhaps
to avoid floating point differences. When you do date_part('julian',
date), then you get a correct Julian Day. But date_part('julian',
timestamp[tz]) gives incorrect Julian Date values that are off by 12
hours. My patch doesn't change that, I just noticed when I took away
the round() call in the regression tests. Those calls now produce a
different number of decimal places.
It might make sense to make date_part(..., date) a separate C function
instead of an SQL wrapper around date_part(..., timestamp). That could
return integer and could reject nonsensical fields such as "minute".
Then we could also make a less contorted implementation of
date_part('julian', date) that matches to_char(date, 'J') and remove the
incorrect implementation of date_part('julian', timestamp).
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Pavel Stehule
Date:
út 4. 8. 2020 v 16:08 odesílatel Peter Eisentraut <peter.eisentraut@2ndquadrant.com> napsal:
On 2020-05-25 15:28, Peter Eisentraut wrote:
> On 2019-12-02 23:52, Thomas Munro wrote:
>>> I'm not an expert in floating point math but hopefully it means that no
>>> type change is required - double precision can handle it.
>> Me neither, but the SQL standard requires us to use an exact numeric
>> type, so it's wrong on that level by definition.
>
> I looked into this (changing the return types of date_part()/extract()
> from float8 to numeric).
>
> One problem (other than perhaps performance, tbd.) is that this would no
> longer allow processing infinite timestamps, since numeric does not
> support infinity. It could be argued that running extract() on infinite
> timestamps isn't very useful, but it's something to consider explicitly.
Now that numeric supports infinity, here is a patch that changes the
return types of date_part() to numeric. It's not meant to be a final
version, but it is useful for discussing a few things.
The internal implementation could be made a bit more elegant if we had
variants of int4_numeric() and int8_numeric() that don't have to go
through fmgr. This would also help in other areas of the code. There
are probably also other ways in which the internals could be made more
compact; I just converted them fairly directly.
When extracting seconds or microseconds, I made it always produce 6 or 3
decimal places, even if they are zero. I don't know if we want that or
what behavior we want. That's what all the changes in the regression
tests are about. Everything else passes unchanged.
The 'julian' field is a bit of a mystery. First of all it's not
documented. The regression tests only test the rounded output, perhaps
to avoid floating point differences. When you do date_part('julian',
date), then you get a correct Julian Day. But date_part('julian',
timestamp[tz]) gives incorrect Julian Date values that are off by 12
hours. My patch doesn't change that, I just noticed when I took away
the round() call in the regression tests. Those calls now produce a
different number of decimal places.
It might make sense to make date_part(..., date) a separate C function
instead of an SQL wrapper around date_part(..., timestamp). That could
return integer and could reject nonsensical fields such as "minute".
Then we could also make a less contorted implementation of
date_part('julian', date) that matches to_char(date, 'J') and remove the
incorrect implementation of date_part('julian', timestamp).
I like a idea to have d date variant of date_part
Pavel
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
Here is a new patch series version. I have created a new internal function for converting integers to numeric, to make the implementation a bit more elegant and compact. I have also created a new date_part(..., date) in C, and added more test coverage for that. Other than some of the semantic issues mentioned in the previous message, this version looks pretty good to me in principle. I have done some performance tests to assess the impact of changing from float to numeric. I did tests like this: create table t1 (a int, b timestamp with time zone); insert into t1 select generate_series(1, 10000000), current_timestamp + random() * interval '1000 days'; select extract(dow from b) from t1 \g /dev/null select extract(epoch from b) from t1 \g /dev/null There appears to be about a 20% increase in run time for these tests. These are obviously extreme tests, so I think that would be okay. More tests and testing ideas are welcome. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Here is a new patch series version.
> I have created a new internal function for converting integers to
> numeric, to make the implementation a bit more elegant and compact.
I reviewed the 0002 patch, finding one bug (in int8_sum) and a few
more calls of int8_numeric that could be converted. I think the
attached updated version is committable, and I'd recommend going
ahead with that regardless of the rest of this. I hadn't realized
how many random calls of int8_numeric and int4_numeric we'd grown,
but there are a lot, so this is nice cleanup.
I continue to think that we can't commit 0003 in this form, because
of the breakage that will ensure in stored views. As I said upthread,
we should leave the existing SQL-exposed functions alone, invent
new ones that return numeric, and alter the parser to translate
EXTRACT constructs to the new functions. This approach would also
provide an "out" for anyone who does complain about the performance
cost --- they can just continue to use the old functions.
regards, tom lane
diff --git a/contrib/btree_gist/btree_numeric.c b/contrib/btree_gist/btree_numeric.c
index d66901680e..35e466cdd9 100644
--- a/contrib/btree_gist/btree_numeric.c
+++ b/contrib/btree_gist/btree_numeric.c
@@ -195,7 +195,7 @@ gbt_numeric_penalty(PG_FUNCTION_ARGS)
}
else
{
- Numeric nul = DatumGetNumeric(DirectFunctionCall1(int4_numeric, Int32GetDatum(0)));
+ Numeric nul = int64_to_numeric(0);
*result = 0.0;
diff --git a/contrib/jsonb_plperl/jsonb_plperl.c b/contrib/jsonb_plperl/jsonb_plperl.c
index b81ba54b80..22e90afe1b 100644
--- a/contrib/jsonb_plperl/jsonb_plperl.c
+++ b/contrib/jsonb_plperl/jsonb_plperl.c
@@ -216,9 +216,7 @@ SV_to_JsonbValue(SV *in, JsonbParseState **jsonb_state, bool is_elem)
IV ival = SvIV(in);
out.type = jbvNumeric;
- out.val.numeric =
- DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- Int64GetDatum((int64) ival)));
+ out.val.numeric = int64_to_numeric(ival);
}
else if (SvNOK(in))
{
diff --git a/src/backend/utils/adt/cash.c b/src/backend/utils/adt/cash.c
index 6515fc8ec6..d093ce8038 100644
--- a/src/backend/utils/adt/cash.c
+++ b/src/backend/utils/adt/cash.c
@@ -1042,7 +1042,7 @@ cash_numeric(PG_FUNCTION_ARGS)
fpoint = 2;
/* convert the integral money value to numeric */
- result = DirectFunctionCall1(int8_numeric, Int64GetDatum(money));
+ result = NumericGetDatum(int64_to_numeric(money));
/* scale appropriately, if needed */
if (fpoint > 0)
@@ -1056,8 +1056,7 @@ cash_numeric(PG_FUNCTION_ARGS)
scale = 1;
for (i = 0; i < fpoint; i++)
scale *= 10;
- numeric_scale = DirectFunctionCall1(int8_numeric,
- Int64GetDatum(scale));
+ numeric_scale = NumericGetDatum(int64_to_numeric(scale));
/*
* Given integral inputs approaching INT64_MAX, select_div_scale()
@@ -1107,7 +1106,7 @@ numeric_cash(PG_FUNCTION_ARGS)
scale *= 10;
/* multiply the input amount by scale factor */
- numeric_scale = DirectFunctionCall1(int8_numeric, Int64GetDatum(scale));
+ numeric_scale = NumericGetDatum(int64_to_numeric(scale));
amount = DirectFunctionCall2(numeric_mul, amount, numeric_scale);
/* note that numeric_int8 will round to nearest integer for us */
diff --git a/src/backend/utils/adt/dbsize.c b/src/backend/utils/adt/dbsize.c
index 2320c06a9b..7def7392b9 100644
--- a/src/backend/utils/adt/dbsize.c
+++ b/src/backend/utils/adt/dbsize.c
@@ -579,14 +579,6 @@ numeric_to_cstring(Numeric n)
return DatumGetCString(DirectFunctionCall1(numeric_out, d));
}
-static Numeric
-int64_to_numeric(int64 v)
-{
- Datum d = Int64GetDatum(v);
-
- return DatumGetNumeric(DirectFunctionCall1(int8_numeric, d));
-}
-
static bool
numeric_is_less(Numeric a, Numeric b)
{
@@ -615,9 +607,9 @@ numeric_half_rounded(Numeric n)
Datum two;
Datum result;
- zero = DirectFunctionCall1(int8_numeric, Int64GetDatum(0));
- one = DirectFunctionCall1(int8_numeric, Int64GetDatum(1));
- two = DirectFunctionCall1(int8_numeric, Int64GetDatum(2));
+ zero = NumericGetDatum(int64_to_numeric(0));
+ one = NumericGetDatum(int64_to_numeric(1));
+ two = NumericGetDatum(int64_to_numeric(2));
if (DatumGetBool(DirectFunctionCall2(numeric_ge, d, zero)))
d = DirectFunctionCall2(numeric_add, d, one);
@@ -632,12 +624,10 @@ static Numeric
numeric_shift_right(Numeric n, unsigned count)
{
Datum d = NumericGetDatum(n);
- Datum divisor_int64;
Datum divisor_numeric;
Datum result;
- divisor_int64 = Int64GetDatum((int64) (1 << count));
- divisor_numeric = DirectFunctionCall1(int8_numeric, divisor_int64);
+ divisor_numeric = NumericGetDatum(int64_to_numeric(1 << count));
result = DirectFunctionCall2(numeric_div_trunc, d, divisor_numeric);
return DatumGetNumeric(result);
}
@@ -832,8 +822,7 @@ pg_size_bytes(PG_FUNCTION_ARGS)
{
Numeric mul_num;
- mul_num = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- Int64GetDatum(multiplier)));
+ mul_num = int64_to_numeric(multiplier);
num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
NumericGetDatum(mul_num),
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 7d09537d82..f9aa968f09 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -6070,10 +6070,8 @@ numeric_to_number(PG_FUNCTION_ARGS)
if (IS_MULTI(&Num))
{
Numeric x;
- Numeric a = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- Int32GetDatum(10)));
- Numeric b = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- Int32GetDatum(-Num.multi)));
+ Numeric a = int64_to_numeric(10);
+ Numeric b = int64_to_numeric(-Num.multi);
x = DatumGetNumeric(DirectFunctionCall2(numeric_power,
NumericGetDatum(a),
@@ -6162,10 +6160,8 @@ numeric_to_char(PG_FUNCTION_ARGS)
if (IS_MULTI(&Num))
{
- Numeric a = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- Int32GetDatum(10)));
- Numeric b = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- Int32GetDatum(Num.multi)));
+ Numeric a = int64_to_numeric(10);
+ Numeric b = int64_to_numeric(Num.multi);
x = DatumGetNumeric(DirectFunctionCall2(numeric_power,
NumericGetDatum(a),
@@ -6339,11 +6335,8 @@ int8_to_char(PG_FUNCTION_ARGS)
else if (IS_EEEE(&Num))
{
/* to avoid loss of precision, must go via numeric not float8 */
- Numeric val;
-
- val = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- Int64GetDatum(value)));
- orgnum = numeric_out_sci(val, Num.post);
+ orgnum = numeric_out_sci(int64_to_numeric(value),
+ Num.post);
/*
* numeric_out_sci() does not emit a sign for positive numbers. We
diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c
index f146767bfc..7403c760b4 100644
--- a/src/backend/utils/adt/jsonpath_exec.c
+++ b/src/backend/utils/adt/jsonpath_exec.c
@@ -842,9 +842,7 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
lastjbv = hasNext ? &tmpjbv : palloc(sizeof(*lastjbv));
lastjbv->type = jbvNumeric;
- lastjbv->val.numeric =
- DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- Int32GetDatum(last)));
+ lastjbv->val.numeric = int64_to_numeric(last);
res = executeNextItem(cxt, jsp, &elem,
lastjbv, found, hasNext);
@@ -1012,9 +1010,7 @@ executeItemOptUnwrapTarget(JsonPathExecContext *cxt, JsonPathItem *jsp,
jb = palloc(sizeof(*jb));
jb->type = jbvNumeric;
- jb->val.numeric =
- DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- Int32GetDatum(size)));
+ jb->val.numeric = int64_to_numeric(size);
res = executeNextItem(cxt, jsp, NULL, jb, found, false);
}
@@ -1979,8 +1975,7 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
id += (int64) cxt->baseObject.id * INT64CONST(10000000000);
idval.type = jbvNumeric;
- idval.val.numeric = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- Int64GetDatum(id)));
+ idval.val.numeric = int64_to_numeric(id);
it = JsonbIteratorInit(jbc);
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index ed825a1fdd..d2cc74b284 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -4073,23 +4073,29 @@ numeric_trim_scale(PG_FUNCTION_ARGS)
* ----------------------------------------------------------------------
*/
-
-Datum
-int4_numeric(PG_FUNCTION_ARGS)
+Numeric
+int64_to_numeric(int64 val)
{
- int32 val = PG_GETARG_INT32(0);
Numeric res;
NumericVar result;
init_var(&result);
- int64_to_numericvar((int64) val, &result);
+ int64_to_numericvar(val, &result);
res = make_result(&result);
free_var(&result);
- PG_RETURN_NUMERIC(res);
+ return res;
+}
+
+Datum
+int4_numeric(PG_FUNCTION_ARGS)
+{
+ int32 val = PG_GETARG_INT32(0);
+
+ PG_RETURN_NUMERIC(int64_to_numeric(val));
}
int32
@@ -4174,18 +4180,8 @@ Datum
int8_numeric(PG_FUNCTION_ARGS)
{
int64 val = PG_GETARG_INT64(0);
- Numeric res;
- NumericVar result;
- init_var(&result);
-
- int64_to_numericvar(val, &result);
-
- res = make_result(&result);
-
- free_var(&result);
-
- PG_RETURN_NUMERIC(res);
+ PG_RETURN_NUMERIC(int64_to_numeric(val));
}
@@ -4224,18 +4220,8 @@ Datum
int2_numeric(PG_FUNCTION_ARGS)
{
int16 val = PG_GETARG_INT16(0);
- Numeric res;
- NumericVar result;
-
- init_var(&result);
-
- int64_to_numericvar((int64) val, &result);
-
- res = make_result(&result);
- free_var(&result);
-
- PG_RETURN_NUMERIC(res);
+ PG_RETURN_NUMERIC(int64_to_numeric(val));
}
@@ -5290,11 +5276,7 @@ int2_accum(PG_FUNCTION_ARGS)
#ifdef HAVE_INT128
do_int128_accum(state, (int128) PG_GETARG_INT16(1));
#else
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int2_numeric,
- PG_GETARG_DATUM(1)));
- do_numeric_accum(state, newval);
+ do_numeric_accum(state, int64_to_numeric(PG_GETARG_INT16(1)));
#endif
}
@@ -5317,11 +5299,7 @@ int4_accum(PG_FUNCTION_ARGS)
#ifdef HAVE_INT128
do_int128_accum(state, (int128) PG_GETARG_INT32(1));
#else
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- PG_GETARG_DATUM(1)));
- do_numeric_accum(state, newval);
+ do_numeric_accum(state, int64_to_numeric(PG_GETARG_INT32(1)));
#endif
}
@@ -5340,13 +5318,7 @@ int8_accum(PG_FUNCTION_ARGS)
state = makeNumericAggState(fcinfo, true);
if (!PG_ARGISNULL(1))
- {
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- PG_GETARG_DATUM(1)));
- do_numeric_accum(state, newval);
- }
+ do_numeric_accum(state, int64_to_numeric(PG_GETARG_INT64(1)));
PG_RETURN_POINTER(state);
}
@@ -5570,11 +5542,7 @@ int8_avg_accum(PG_FUNCTION_ARGS)
#ifdef HAVE_INT128
do_int128_accum(state, (int128) PG_GETARG_INT64(1));
#else
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- PG_GETARG_DATUM(1)));
- do_numeric_accum(state, newval);
+ do_numeric_accum(state, int64_to_numeric(PG_GETARG_INT64(1)));
#endif
}
@@ -5767,13 +5735,8 @@ int2_accum_inv(PG_FUNCTION_ARGS)
#ifdef HAVE_INT128
do_int128_discard(state, (int128) PG_GETARG_INT16(1));
#else
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int2_numeric,
- PG_GETARG_DATUM(1)));
-
/* Should never fail, all inputs have dscale 0 */
- if (!do_numeric_discard(state, newval))
+ if (!do_numeric_discard(state, int64_to_numeric(PG_GETARG_INT16(1))))
elog(ERROR, "do_numeric_discard failed unexpectedly");
#endif
}
@@ -5797,13 +5760,8 @@ int4_accum_inv(PG_FUNCTION_ARGS)
#ifdef HAVE_INT128
do_int128_discard(state, (int128) PG_GETARG_INT32(1));
#else
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
- PG_GETARG_DATUM(1)));
-
/* Should never fail, all inputs have dscale 0 */
- if (!do_numeric_discard(state, newval))
+ if (!do_numeric_discard(state, int64_to_numeric(PG_GETARG_INT32(1))))
elog(ERROR, "do_numeric_discard failed unexpectedly");
#endif
}
@@ -5824,13 +5782,8 @@ int8_accum_inv(PG_FUNCTION_ARGS)
if (!PG_ARGISNULL(1))
{
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- PG_GETARG_DATUM(1)));
-
/* Should never fail, all inputs have dscale 0 */
- if (!do_numeric_discard(state, newval))
+ if (!do_numeric_discard(state, int64_to_numeric(PG_GETARG_INT64(1))))
elog(ERROR, "do_numeric_discard failed unexpectedly");
}
@@ -5853,13 +5806,8 @@ int8_avg_accum_inv(PG_FUNCTION_ARGS)
#ifdef HAVE_INT128
do_int128_discard(state, (int128) PG_GETARG_INT64(1));
#else
- Numeric newval;
-
- newval = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
- PG_GETARG_DATUM(1)));
-
/* Should never fail, all inputs have dscale 0 */
- if (!do_numeric_discard(state, newval))
+ if (!do_numeric_discard(state, int64_to_numeric(PG_GETARG_INT64(1))))
elog(ERROR, "do_numeric_discard failed unexpectedly");
#endif
}
@@ -5914,8 +5862,7 @@ numeric_poly_avg(PG_FUNCTION_ARGS)
int128_to_numericvar(state->sumX, &result);
- countd = DirectFunctionCall1(int8_numeric,
- Int64GetDatumFast(state->N));
+ countd = NumericGetDatum(int64_to_numeric(state->N));
sumd = NumericGetDatum(make_result(&result));
free_var(&result);
@@ -5951,7 +5898,7 @@ numeric_avg(PG_FUNCTION_ARGS)
if (state->nInfcount > 0)
PG_RETURN_NUMERIC(make_result(&const_ninf));
- N_datum = DirectFunctionCall1(int8_numeric, Int64GetDatum(state->N));
+ N_datum = NumericGetDatum(int64_to_numeric(state->N));
init_var(&sumX_var);
accum_sum_final(&state->sumX, &sumX_var);
@@ -6411,7 +6358,6 @@ Datum
int8_sum(PG_FUNCTION_ARGS)
{
Numeric oldsum;
- Datum newval;
if (PG_ARGISNULL(0))
{
@@ -6419,8 +6365,7 @@ int8_sum(PG_FUNCTION_ARGS)
if (PG_ARGISNULL(1))
PG_RETURN_NULL(); /* still no non-null */
/* This is the first non-null input. */
- newval = DirectFunctionCall1(int8_numeric, PG_GETARG_DATUM(1));
- PG_RETURN_DATUM(newval);
+ PG_RETURN_NUMERIC(int64_to_numeric(PG_GETARG_INT64(1)));
}
/*
@@ -6436,10 +6381,9 @@ int8_sum(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(oldsum);
/* OK to do the addition. */
- newval = DirectFunctionCall1(int8_numeric, PG_GETARG_DATUM(1));
-
PG_RETURN_DATUM(DirectFunctionCall2(numeric_add,
- NumericGetDatum(oldsum), newval));
+ NumericGetDatum(oldsum),
+ NumericGetDatum(int64_to_numeric(PG_GETARG_INT64(1)))));
}
@@ -6618,10 +6562,8 @@ int8_avg(PG_FUNCTION_ARGS)
if (transdata->count == 0)
PG_RETURN_NULL();
- countd = DirectFunctionCall1(int8_numeric,
- Int64GetDatumFast(transdata->count));
- sumd = DirectFunctionCall1(int8_numeric,
- Int64GetDatumFast(transdata->sum));
+ countd = NumericGetDatum(int64_to_numeric(transdata->count));
+ sumd = NumericGetDatum(int64_to_numeric(transdata->sum));
PG_RETURN_DATUM(DirectFunctionCall2(numeric_div, sumd, countd));
}
diff --git a/src/include/utils/numeric.h b/src/include/utils/numeric.h
index 0b7d4ba3c4..2a768b9a04 100644
--- a/src/include/utils/numeric.h
+++ b/src/include/utils/numeric.h
@@ -62,6 +62,8 @@ int32 numeric_maximum_size(int32 typmod);
extern char *numeric_out_sci(Numeric num, int scale);
extern char *numeric_normalize(Numeric num);
+extern Numeric int64_to_numeric(int64 val);
+
extern Numeric numeric_add_opt_error(Numeric num1, Numeric num2,
bool *have_error);
extern Numeric numeric_sub_opt_error(Numeric num1, Numeric num2,
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Pavel Stehule
Date:
po 7. 9. 2020 v 1:46 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Here is a new patch series version.
> I have created a new internal function for converting integers to
> numeric, to make the implementation a bit more elegant and compact.
I reviewed the 0002 patch, finding one bug (in int8_sum) and a few
more calls of int8_numeric that could be converted. I think the
attached updated version is committable, and I'd recommend going
ahead with that regardless of the rest of this. I hadn't realized
how many random calls of int8_numeric and int4_numeric we'd grown,
but there are a lot, so this is nice cleanup.
This patch is a clean win.
+1
I continue to think that we can't commit 0003 in this form, because
of the breakage that will ensure in stored views. As I said upthread,
we should leave the existing SQL-exposed functions alone, invent
new ones that return numeric, and alter the parser to translate
EXTRACT constructs to the new functions. This approach would also
provide an "out" for anyone who does complain about the performance
cost --- they can just continue to use the old functions.
+1
Regards
Pavel
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 2020-09-07 01:46, Tom Lane wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> Here is a new patch series version. >> I have created a new internal function for converting integers to >> numeric, to make the implementation a bit more elegant and compact. > > I reviewed the 0002 patch, finding one bug (in int8_sum) Ouch, no test coverage. Should we perhaps remove this function, since it's obsolete and unused? > and a few > more calls of int8_numeric that could be converted. I think the > attached updated version is committable, and I'd recommend going > ahead with that regardless of the rest of this. I hadn't realized > how many random calls of int8_numeric and int4_numeric we'd grown, > but there are a lot, so this is nice cleanup. Yes, please go ahead with it. > I continue to think that we can't commit 0003 in this form, because > of the breakage that will ensure in stored views. As I said upthread, > we should leave the existing SQL-exposed functions alone, invent > new ones that return numeric, and alter the parser to translate > EXTRACT constructs to the new functions. This approach would also > provide an "out" for anyone who does complain about the performance > cost --- they can just continue to use the old functions. Okay, I will continue looking into this. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 2020-09-07 01:46, Tom Lane wrote:
>> I reviewed the 0002 patch, finding one bug (in int8_sum)
> Ouch, no test coverage. Should we perhaps remove this function, since
> it's obsolete and unused?
I don't feel a need to.
>> and a few
>> more calls of int8_numeric that could be converted. I think the
>> attached updated version is committable, and I'd recommend going
>> ahead with that regardless of the rest of this. I hadn't realized
>> how many random calls of int8_numeric and int4_numeric we'd grown,
>> but there are a lot, so this is nice cleanup.
> Yes, please go ahead with it.
It's your patch, I figured you'd want to commit it.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 2020-09-09 15:38, Tom Lane wrote: >>> and a few >>> more calls of int8_numeric that could be converted. I think the >>> attached updated version is committable, and I'd recommend going >>> ahead with that regardless of the rest of this. I hadn't realized >>> how many random calls of int8_numeric and int4_numeric we'd grown, >>> but there are a lot, so this is nice cleanup. > >> Yes, please go ahead with it. > > It's your patch, I figured you'd want to commit it. ok done -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Michael Paquier
Date:
On Wed, Sep 09, 2020 at 08:47:36PM +0200, Peter Eisentraut wrote: > ok done As far as I can see, patches 0001 and 0002 have been already applied, but not 0003. Could you send a rebase to allow the CF bot to run, at least? -- Michael
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Michael Paquier
Date:
On Thu, Sep 17, 2020 at 02:53:27PM +0900, Michael Paquier wrote: > As far as I can see, patches 0001 and 0002 have been already applied, > but not 0003. Could you send a rebase to allow the CF bot to run, at > least? This was two weeks ago. Looking at 0003, the thing is not really complicated, but as this thread has stalled I have marked the entry as RwF. -- Michael
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
I wrote:
> However: suppose that we continue to translate these things into FuncExpr
> nodes, the same as always, but we add a new CoercionForm variant, say
> COERCE_SQL_SYNTAX. 99% of the system ignores FuncExpr.funcformat,
> and would continue to do so, but ruleutils.c would take it to mean
> that (1) the call should be reverse-listed as some special SQL syntax
> and (2) the funcid is one of a small set of built-in functions for
> which ruleutils.c knows what to emit. (If it doesn't recognize the
> funcid, it could either throw an error, or fall back to normal display
> of the node.) For cases such as EXTRACT, this would also represent
> a promise that specific arguments are Const nodes from which the
> desired keyword can be extracted.
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:
* Some C functions such as text_substr have multiple pg_proc entries,
and Gen_fmgrtab.pl chooses the wrong one for our purpose. We could
either invent new Gen_fmgrtab.pl behavior to allow having macros for
all the pg_proc entries, or we could add duplicate C functions so that
the pg_proc entries can point to different C symbols.
* Some of the functions we need to reference aren't C functions at
all, but SQL functions, for instance OID 1305 is defined as
select ($1, ($1 + $2)) overlaps ($3, ($3 + $4))
I think our best bet here is to replace these SQL definitions with
C equivalents, because really this implementation is pretty sucky.
Even if we manage to inline the SQL definition, that's expensive
to do; and evaluating some of the arguments twice is not nice either.
> This is kind of an abuse of "CoercionForm", since that typedef name
> implies that it only talks about how to handle cast cases, but
> semantically it's always been a how-to-display-function-calls thing.
> We could either hold our noses about that or rename the typedef.
I did nothing about that here, since it'd bloat the patch without
making anything but cosmetic changes. I'm tempted to propose though
that we rename "CoercionForm" to "DisplayForm" and rename its
COERCE_XXX values to DISPLAY_XXX, to make this less confusing.
Another bit of follow-up work we could contemplate is to get rid of
the SQLValueFunction node type, since there's nothing it does that
we couldn't do with regular FuncExpr nodes and COERCE_SQL_SYNTAX.
But that's just cleanup, and I don't think it would save a very
large amount of code.
Thoughts?
regards, tom lane
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 2b4d7654cc..f14236ad3a 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2682,11 +2682,12 @@ _copyFuncCall(const FuncCall *from)
COPY_NODE_FIELD(args);
COPY_NODE_FIELD(agg_order);
COPY_NODE_FIELD(agg_filter);
+ COPY_NODE_FIELD(over);
COPY_SCALAR_FIELD(agg_within_group);
COPY_SCALAR_FIELD(agg_star);
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
- COPY_NODE_FIELD(over);
+ COPY_SCALAR_FIELD(funcformat);
COPY_LOCATION_FIELD(location);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index e2d1b987bf..8985b11f8f 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2377,11 +2377,12 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
COMPARE_NODE_FIELD(args);
COMPARE_NODE_FIELD(agg_order);
COMPARE_NODE_FIELD(agg_filter);
+ COMPARE_NODE_FIELD(over);
COMPARE_SCALAR_FIELD(agg_within_group);
COMPARE_SCALAR_FIELD(agg_star);
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
- COMPARE_NODE_FIELD(over);
+ COMPARE_SCALAR_FIELD(funcformat);
COMPARE_LOCATION_FIELD(location);
return true;
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 49de285f01..ee033ae779 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -582,7 +582,7 @@ makeDefElemExtended(char *nameSpace, char *name, Node *arg,
* supply. Any non-default parameters have to be inserted by the caller.
*/
FuncCall *
-makeFuncCall(List *name, List *args, int location)
+makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
{
FuncCall *n = makeNode(FuncCall);
@@ -590,11 +590,12 @@ makeFuncCall(List *name, List *args, int location)
n->args = args;
n->agg_order = NIL;
n->agg_filter = NULL;
+ n->over = NULL;
n->agg_within_group = false;
n->agg_star = false;
n->agg_distinct = false;
n->func_variadic = false;
- n->over = NULL;
+ n->funcformat = funcformat;
n->location = location;
return n;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 530328af43..cafe19b2f0 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2766,11 +2766,12 @@ _outFuncCall(StringInfo str, const FuncCall *node)
WRITE_NODE_FIELD(args);
WRITE_NODE_FIELD(agg_order);
WRITE_NODE_FIELD(agg_filter);
+ WRITE_NODE_FIELD(over);
WRITE_BOOL_FIELD(agg_within_group);
WRITE_BOOL_FIELD(agg_star);
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
- WRITE_NODE_FIELD(over);
+ WRITE_ENUM_FIELD(funcformat, CoercionForm);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 480d168346..20e840ce20 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -12977,6 +12977,7 @@ a_expr: c_expr { $$ = $1; }
{
$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
list_make2($5, $1),
+ COERCE_SQL_SYNTAX,
@2);
}
/*
@@ -13040,6 +13041,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($3, $5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_LIKE, "~~",
$1, (Node *) n, @2);
@@ -13053,6 +13055,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_LIKE, "!~~",
$1, (Node *) n, @2);
@@ -13066,6 +13069,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($3, $5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_ILIKE, "~~*",
$1, (Node *) n, @2);
@@ -13079,6 +13083,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_ILIKE, "!~~*",
$1, (Node *) n, @2);
@@ -13088,6 +13093,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make1($4),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "~",
$1, (Node *) n, @2);
@@ -13096,6 +13102,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "~",
$1, (Node *) n, @2);
@@ -13104,6 +13111,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make1($5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "!~",
$1, (Node *) n, @2);
@@ -13112,6 +13120,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make2($5, $7),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "!~",
$1, (Node *) n, @2);
@@ -13172,6 +13181,7 @@ a_expr: c_expr { $$ = $1; }
parser_errposition(@3)));
$$ = (Node *) makeFuncCall(SystemFuncName("overlaps"),
list_concat($1, $3),
+ COERCE_SQL_SYNTAX,
@2);
}
| a_expr IS TRUE_P %prec IS
@@ -13359,19 +13369,33 @@ a_expr: c_expr { $$ = $1; }
}
| a_expr IS NORMALIZED %prec IS
{
- $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make1($1), @2);
+ $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make1($1),
+ COERCE_SQL_SYNTAX,
+ @2);
}
| a_expr IS unicode_normal_form NORMALIZED %prec IS
{
- $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make2($1, makeStringConst($3,
@3)),@2);
+ $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make2($1, makeStringConst($3, @3)),
+ COERCE_SQL_SYNTAX,
+ @2);
}
| a_expr IS NOT NORMALIZED %prec IS
{
- $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make1($1), @2), @2);
+ $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make1($1),
+ COERCE_SQL_SYNTAX,
+ @2),
+ @2);
}
| a_expr IS NOT unicode_normal_form NORMALIZED %prec IS
{
- $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make2($1,
makeStringConst($4,@4)), @2), @2);
+ $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make2($1, makeStringConst($4, @4)),
+ COERCE_SQL_SYNTAX,
+ @2),
+ @2);
}
| DEFAULT
{
@@ -13621,31 +13645,41 @@ c_expr: columnref { $$ = $1; }
func_application: func_name '(' ')'
{
- $$ = (Node *) makeFuncCall($1, NIL, @1);
+ $$ = (Node *) makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| func_name '(' func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $3, @1);
+ FuncCall *n = makeFuncCall($1, $3,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $4;
$$ = (Node *)n;
}
| func_name '(' VARIADIC func_arg_expr opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, list_make1($4), @1);
+ FuncCall *n = makeFuncCall($1, list_make1($4),
+ COERCE_EXPLICIT_CALL,
+ @1);
n->func_variadic = true;
n->agg_order = $5;
$$ = (Node *)n;
}
| func_name '(' func_arg_list ',' VARIADIC func_arg_expr opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, lappend($3, $6), @1);
+ FuncCall *n = makeFuncCall($1, lappend($3, $6),
+ COERCE_EXPLICIT_CALL,
+ @1);
n->func_variadic = true;
n->agg_order = $7;
$$ = (Node *)n;
}
| func_name '(' ALL func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $4, @1);
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $5;
/* Ideally we'd mark the FuncCall node to indicate
* "must be an aggregate", but there's no provision
@@ -13655,7 +13689,9 @@ func_application: func_name '(' ')'
}
| func_name '(' DISTINCT func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $4, @1);
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $5;
n->agg_distinct = true;
$$ = (Node *)n;
@@ -13672,7 +13708,9 @@ func_application: func_name '(' ')'
* so that later processing can detect what the argument
* really was.
*/
- FuncCall *n = makeFuncCall($1, NIL, @1);
+ FuncCall *n = makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_star = true;
$$ = (Node *)n;
}
@@ -13746,6 +13784,7 @@ func_expr_common_subexpr:
{
$$ = (Node *) makeFuncCall(SystemFuncName("pg_collation_for"),
list_make1($4),
+ COERCE_SQL_SYNTAX,
@1);
}
| CURRENT_DATE
@@ -13812,31 +13851,49 @@ func_expr_common_subexpr:
{ $$ = makeTypeCast($3, $5, @1); }
| EXTRACT '(' extract_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("date_part"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("date_part"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NORMALIZE '(' a_expr ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("normalize"), list_make1($3), @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("normalize"),
+ list_make1($3),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NORMALIZE '(' a_expr ',' unicode_normal_form ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("normalize"), list_make2($3, makeStringConst($5, @5)),
@1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("normalize"),
+ list_make2($3, makeStringConst($5, @5)),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| OVERLAY '(' overlay_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("overlay"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("overlay"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| POSITION '(' position_list ')'
{
/* position(A in B) is converted to position(B, A) */
- $$ = (Node *) makeFuncCall(SystemFuncName("position"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("position"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| SUBSTRING '(' substr_list ')'
{
/* substring(A from B for C) is converted to
* substring(A, B, C) - thomas 2000-11-28
*/
- $$ = (Node *) makeFuncCall(SystemFuncName("substring"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("substring"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TREAT '(' a_expr AS Typename ')'
{
@@ -13849,28 +13906,41 @@ func_expr_common_subexpr:
* Convert SystemTypeName() to SystemFuncName() even though
* at the moment they result in the same thing.
*/
- $$ = (Node *) makeFuncCall(SystemFuncName(((Value *)llast($5->names))->val.str),
- list_make1($3),
- @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName(((Value *) llast($5->names))->val.str),
+ list_make1($3),
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| TRIM '(' BOTH trim_list ')'
{
/* various trim expressions are defined in SQL
* - thomas 1997-07-19
*/
- $$ = (Node *) makeFuncCall(SystemFuncName("btrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("btrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' LEADING trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("ltrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("ltrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' TRAILING trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("rtrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("rtrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("btrim"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("btrim"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NULLIF '(' a_expr ',' a_expr ')'
{
@@ -13923,7 +13993,10 @@ func_expr_common_subexpr:
{
/* xmlexists(A PASSING [BY REF] B [BY REF]) is
* converted to xmlexists(A, B)*/
- $$ = (Node *) makeFuncCall(SystemFuncName("xmlexists"), list_make2($3, $4), @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("xmlexists"),
+ list_make2($3, $4),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| XMLFOREST '(' xml_attribute_list ')'
{
@@ -14453,10 +14526,10 @@ extract_arg:
;
unicode_normal_form:
- NFC { $$ = "nfc"; }
- | NFD { $$ = "nfd"; }
- | NFKC { $$ = "nfkc"; }
- | NFKD { $$ = "nfkd"; }
+ NFC { $$ = "NFC"; }
+ | NFD { $$ = "NFD"; }
+ | NFKC { $$ = "NFKC"; }
+ | NFKD { $$ = "NFKD"; }
;
/* OVERLAY() arguments */
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 7460e61160..ea4a1f5aeb 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -541,10 +541,11 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
list_length(fc->args) > 1 &&
fc->agg_order == NIL &&
fc->agg_filter == NULL &&
+ fc->over == NULL &&
!fc->agg_star &&
!fc->agg_distinct &&
!fc->func_variadic &&
- fc->over == NULL &&
+ fc->funcformat == COERCE_EXPLICIT_CALL &&
coldeflist == NIL)
{
ListCell *lc;
@@ -558,6 +559,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
newfc = makeFuncCall(SystemFuncName("unnest"),
list_make1(arg),
+ COERCE_EXPLICIT_CALL,
fc->location);
newfexpr = transformExpr(pstate, (Node *) newfc,
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index a7a31704fb..8b4e3ca5e1 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -91,11 +91,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool is_column = (fn == NULL);
List *agg_order = (fn ? fn->agg_order : NIL);
Expr *agg_filter = NULL;
+ WindowDef *over = (fn ? fn->over : NULL);
bool agg_within_group = (fn ? fn->agg_within_group : false);
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
- WindowDef *over = (fn ? fn->over : NULL);
+ CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
Oid rettype;
Oid funcid;
@@ -221,6 +222,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
agg_order == NIL && agg_filter == NULL &&
!agg_star && !agg_distinct && over == NULL &&
!func_variadic && argnames == NIL &&
+ funcformat == COERCE_EXPLICIT_CALL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
ISCOMPLEX(actual_arg_types[0])));
@@ -742,7 +744,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
funcexpr->funcresulttype = rettype;
funcexpr->funcretset = retset;
funcexpr->funcvariadic = func_variadic;
- funcexpr->funcformat = COERCE_EXPLICIT_CALL;
+ funcexpr->funcformat = funcformat;
/* funccollid and inputcollid will be set by parse_collate.c */
funcexpr->args = fargs;
funcexpr->location = location;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 015b0538e3..254c0f65c2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -604,6 +604,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
castnode->location = -1;
funccallnode = makeFuncCall(SystemFuncName("nextval"),
list_make1(castnode),
+ COERCE_EXPLICIT_CALL,
-1);
constraint = makeNode(Constraint);
constraint->contype = CONSTR_DEFAULT;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 6c656586e8..407719e494 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -443,6 +443,7 @@ static void get_agg_expr(Aggref *aggref, deparse_context *context,
static void get_agg_combine_expr(Node *node, deparse_context *context,
void *callback_arg);
static void get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context);
+static bool get_func_sql_syntax(FuncExpr *expr, deparse_context *context);
static void get_coercion_expr(Node *arg, deparse_context *context,
Oid resulttype, int32 resulttypmod,
Node *parentNode);
@@ -9159,7 +9160,8 @@ looks_like_function(Node *node)
{
case T_FuncExpr:
/* OK, unless it's going to deparse as a cast */
- return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL);
+ return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL ||
+ ((FuncExpr *) node)->funcformat == COERCE_SQL_SYNTAX);
case T_NullIfExpr:
case T_CoalesceExpr:
case T_MinMaxExpr:
@@ -9261,6 +9263,17 @@ get_func_expr(FuncExpr *expr, deparse_context *context,
return;
}
+ /*
+ * If the function was called using one of the SQL spec's random special
+ * syntaxes, try to reproduce that. If we don't recognize the function,
+ * fall through.
+ */
+ if (expr->funcformat == COERCE_SQL_SYNTAX)
+ {
+ if (get_func_sql_syntax(expr, context))
+ return;
+ }
+
/*
* Normal function: display as proname(args). First we need to extract
* the argument datatypes.
@@ -9496,6 +9509,204 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
}
}
+/*
+ * get_func_sql_syntax - Parse back a SQL-syntax function call
+ *
+ * Returns true if we successfully deparsed, false if we did not
+ * recognize the function.
+ */
+static bool
+get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ Oid funcoid = expr->funcid;
+
+ switch (funcoid)
+ {
+ case F_TIMESTAMP_IZONE:
+ case F_TIMESTAMP_ZONE:
+ case F_TIMESTAMPTZ_IZONE:
+ case F_TIMESTAMPTZ_ZONE:
+ case F_TIMETZ_IZONE:
+ case F_TIMETZ_ZONE:
+ /* AT TIME ZONE ... note reversed argument order */
+ appendStringInfoChar(buf, '(');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " AT TIME ZONE ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ /* XXX this fails to cover all built-in "overlaps" functions */
+ case F_OVERLAPS_TIME:
+ case F_OVERLAPS_TIMESTAMP:
+ case F_OVERLAPS_TIMETZ:
+ /* (x1, x2) OVERLAPS (y1, y2) */
+ appendStringInfoString(buf, "((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, ") OVERLAPS (");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) lfourth(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+
+ case F_UNICODE_IS_NORMALIZED:
+ /* IS xxx NORMALIZED */
+ appendStringInfoString(buf, "((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ") IS");
+ if (list_length(expr->args) == 2)
+ {
+ Const *con = (Const *) lsecond(expr->args);
+
+ Assert(IsA(con, Const) &&
+ con->consttype == TEXTOID &&
+ !con->constisnull);
+ appendStringInfo(buf, " %s",
+ TextDatumGetCString(con->constvalue));
+ }
+ appendStringInfoString(buf, " NORMALIZED)");
+ return true;
+
+ case F_PG_COLLATION_FOR:
+ /* COLLATION FOR */
+ appendStringInfoString(buf, "COLLATION FOR (");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ /*
+ * XXX EXTRACT, a/k/a date_part(), is intentionally not covered
+ * yet. Add it after we change the return type to numeric.
+ */
+
+ case F_UNICODE_NORMALIZE_FUNC:
+ /* NORMALIZE() */
+ appendStringInfoString(buf, "NORMALIZE(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ if (list_length(expr->args) == 2)
+ {
+ Const *con = (Const *) lsecond(expr->args);
+
+ Assert(IsA(con, Const) &&
+ con->consttype == TEXTOID &&
+ !con->constisnull);
+ appendStringInfo(buf, ", %s",
+ TextDatumGetCString(con->constvalue));
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_BITOVERLAY:
+ case F_BITOVERLAY_NO_LEN:
+ case F_BYTEAOVERLAY:
+ case F_BYTEAOVERLAY_NO_LEN:
+ case F_TEXTOVERLAY:
+ case F_TEXTOVERLAY_NO_LEN:
+ /* OVERLAY() */
+ appendStringInfoString(buf, "OVERLAY(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " PLACING ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ if (list_length(expr->args) == 4)
+ {
+ appendStringInfoString(buf, " FOR ");
+ get_rule_expr((Node *) lfourth(expr->args), context, false);
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_BITPOSITION:
+ case F_BYTEAPOS:
+ case F_TEXTPOS:
+ /* POSITION() ... extra parens since args are b_expr not a_expr */
+ appendStringInfoString(buf, "POSITION((");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, ") IN (");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+
+ case F_BITSUBSTR:
+ case F_BITSUBSTR_NO_LEN:
+ case F_BYTEA_SUBSTR:
+ case F_BYTEA_SUBSTR_NO_LEN:
+ case F_TEXT_SUBSTR: /* XXX fails, because this is the wrong OID */
+ case F_TEXT_SUBSTR_NO_LEN: /* XXX fails likewise */
+ case F_TEXTREGEXSUBSTR:
+ /* SUBSTRING FROM/FOR */
+ appendStringInfoString(buf, "SUBSTRING(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ if (list_length(expr->args) == 3)
+ {
+ appendStringInfoString(buf, " FOR ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_BTRIM:
+ case F_BTRIM1:
+ case F_BYTEATRIM:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(BOTH");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_LTRIM:
+ case F_LTRIM1:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(LEADING");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_RTRIM:
+ case F_RTRIM1:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(TRAILING");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_XMLEXISTS:
+ /* XMLEXISTS ... extra parens because args are c_expr */
+ appendStringInfoString(buf, "XMLEXISTS((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ") PASSING (");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+ }
+ return false;
+}
+
/* ----------
* get_coercion_expr
*
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 31d9aedeeb..7ebd794713 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -79,7 +79,8 @@ extern ColumnDef *makeColumnDef(const char *colname,
extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype, List *args,
Oid funccollid, Oid inputcollid, CoercionForm fformat);
-extern FuncCall *makeFuncCall(List *name, List *args, int location);
+extern FuncCall *makeFuncCall(List *name, List *args,
+ CoercionForm funcformat, int location);
extern Expr *make_opclause(Oid opno, Oid opresulttype, bool opretset,
Expr *leftop, Expr *rightop,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ff584f2955..34090eddf9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -353,11 +353,12 @@ typedef struct FuncCall
List *args; /* the arguments (list of exprs) */
List *agg_order; /* ORDER BY (list of SortBy) */
Node *agg_filter; /* FILTER clause, if any */
+ struct WindowDef *over; /* OVER clause, if any */
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
- struct WindowDef *over; /* OVER clause, if any */
+ CoercionForm funcformat; /* how to display this node */
int location; /* token location, or -1 if unknown */
} FuncCall;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index fd65ee8f9c..5b190bb99b 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -445,7 +445,10 @@ typedef enum CoercionContext
} CoercionContext;
/*
- * CoercionForm - how to display a node that could have come from a cast
+ * CoercionForm - how to display a FuncExpr or related node
+ *
+ * "Coercion" is a bit of a misnomer, since this value records other
+ * special syntaxes besides casts, but for now we'll keep this naming.
*
* NB: equal() ignores CoercionForm fields, therefore this *must* not carry
* any semantically significant information. We need that behavior so that
@@ -457,7 +460,8 @@ typedef enum CoercionForm
{
COERCE_EXPLICIT_CALL, /* display as a function call */
COERCE_EXPLICIT_CAST, /* display as an explicit cast */
- COERCE_IMPLICIT_CAST /* implicit cast, so hide it */
+ COERCE_IMPLICIT_CAST, /* implicit cast, so hide it */
+ COERCE_SQL_SYNTAX /* display with SQL-mandated special syntax */
} CoercionForm;
/*
diff --git a/src/test/modules/test_rls_hooks/test_rls_hooks.c b/src/test/modules/test_rls_hooks/test_rls_hooks.c
index 0bfa878a25..c0aaabdcdb 100644
--- a/src/test/modules/test_rls_hooks/test_rls_hooks.c
+++ b/src/test/modules/test_rls_hooks/test_rls_hooks.c
@@ -95,7 +95,10 @@ test_rls_hooks_permissive(CmdType cmdtype, Relation relation)
*/
n = makeFuncCall(list_make2(makeString("pg_catalog"),
- makeString("current_user")), NIL, 0);
+ makeString("current_user")),
+ NIL,
+ COERCE_EXPLICIT_CALL,
+ -1);
c = makeNode(ColumnRef);
c->fields = list_make1(makeString("username"));
@@ -155,7 +158,10 @@ test_rls_hooks_restrictive(CmdType cmdtype, Relation relation)
policy->roles = construct_array(&role, 1, OIDOID, sizeof(Oid), true, TYPALIGN_INT);
n = makeFuncCall(list_make2(makeString("pg_catalog"),
- makeString("current_user")), NIL, 0);
+ makeString("current_user")),
+ NIL,
+ COERCE_EXPLICIT_CALL,
+ -1);
c = makeNode(ColumnRef);
c->fields = list_make1(makeString("supervisor"));
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f10a3a7a12..a3a3b12941 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1710,7 +1710,7 @@ select pg_get_viewdef('tt20v', true);
i4.i4, +
i8.i8 +
FROM COALESCE(1, 2) c(c), +
- pg_collation_for('x'::text) col(col), +
+ COLLATION FOR ('x'::text) col(col), +
CURRENT_DATE d(d), +
LOCALTIMESTAMP(3) t(t), +
CAST(1 + 2 AS integer) i4(i4), +
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 639b50308e..c300965554 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2726,10 +2726,10 @@ create temp table tmptz (f1 timestamptz primary key);
insert into tmptz values ('2017-01-18 00:00+00');
explain (costs off)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on tmptz
- Filter: (timezone('utc'::text, f1) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
+ Filter: ((f1 AT TIME ZONE 'utc'::text) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
(2 rows)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
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
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 530aac68a7..3031c52991 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2682,11 +2682,12 @@ _copyFuncCall(const FuncCall *from)
COPY_NODE_FIELD(args);
COPY_NODE_FIELD(agg_order);
COPY_NODE_FIELD(agg_filter);
+ COPY_NODE_FIELD(over);
COPY_SCALAR_FIELD(agg_within_group);
COPY_SCALAR_FIELD(agg_star);
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
- COPY_NODE_FIELD(over);
+ COPY_SCALAR_FIELD(funcformat);
COPY_LOCATION_FIELD(location);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 0cf90ef33c..9aa853748d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2369,11 +2369,12 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
COMPARE_NODE_FIELD(args);
COMPARE_NODE_FIELD(agg_order);
COMPARE_NODE_FIELD(agg_filter);
+ COMPARE_NODE_FIELD(over);
COMPARE_SCALAR_FIELD(agg_within_group);
COMPARE_SCALAR_FIELD(agg_star);
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
- COMPARE_NODE_FIELD(over);
+ COMPARE_SCALAR_FIELD(funcformat);
COMPARE_LOCATION_FIELD(location);
return true;
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 49de285f01..ee033ae779 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -582,7 +582,7 @@ makeDefElemExtended(char *nameSpace, char *name, Node *arg,
* supply. Any non-default parameters have to be inserted by the caller.
*/
FuncCall *
-makeFuncCall(List *name, List *args, int location)
+makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
{
FuncCall *n = makeNode(FuncCall);
@@ -590,11 +590,12 @@ makeFuncCall(List *name, List *args, int location)
n->args = args;
n->agg_order = NIL;
n->agg_filter = NULL;
+ n->over = NULL;
n->agg_within_group = false;
n->agg_star = false;
n->agg_distinct = false;
n->func_variadic = false;
- n->over = NULL;
+ n->funcformat = funcformat;
n->location = location;
return n;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 7e324c12e2..4504b1503b 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2765,11 +2765,12 @@ _outFuncCall(StringInfo str, const FuncCall *node)
WRITE_NODE_FIELD(args);
WRITE_NODE_FIELD(agg_order);
WRITE_NODE_FIELD(agg_filter);
+ WRITE_NODE_FIELD(over);
WRITE_BOOL_FIELD(agg_within_group);
WRITE_BOOL_FIELD(agg_star);
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
- WRITE_NODE_FIELD(over);
+ WRITE_ENUM_FIELD(funcformat, CoercionForm);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 357ab93fb6..4641050dab 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -12969,6 +12969,7 @@ a_expr: c_expr { $$ = $1; }
{
$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
list_make2($5, $1),
+ COERCE_SQL_SYNTAX,
@2);
}
/*
@@ -13032,6 +13033,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($3, $5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_LIKE, "~~",
$1, (Node *) n, @2);
@@ -13045,6 +13047,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_LIKE, "!~~",
$1, (Node *) n, @2);
@@ -13058,6 +13061,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($3, $5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_ILIKE, "~~*",
$1, (Node *) n, @2);
@@ -13071,6 +13075,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_ILIKE, "!~~*",
$1, (Node *) n, @2);
@@ -13080,6 +13085,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make1($4),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "~",
$1, (Node *) n, @2);
@@ -13088,6 +13094,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "~",
$1, (Node *) n, @2);
@@ -13096,6 +13103,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make1($5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "!~",
$1, (Node *) n, @2);
@@ -13104,6 +13112,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make2($5, $7),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "!~",
$1, (Node *) n, @2);
@@ -13164,6 +13173,7 @@ a_expr: c_expr { $$ = $1; }
parser_errposition(@3)));
$$ = (Node *) makeFuncCall(SystemFuncName("overlaps"),
list_concat($1, $3),
+ COERCE_SQL_SYNTAX,
@2);
}
| a_expr IS TRUE_P %prec IS
@@ -13351,19 +13361,33 @@ a_expr: c_expr { $$ = $1; }
}
| a_expr IS NORMALIZED %prec IS
{
- $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make1($1), @2);
+ $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make1($1),
+ COERCE_SQL_SYNTAX,
+ @2);
}
| a_expr IS unicode_normal_form NORMALIZED %prec IS
{
- $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make2($1, makeStringConst($3,
@3)),@2);
+ $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make2($1, makeStringConst($3, @3)),
+ COERCE_SQL_SYNTAX,
+ @2);
}
| a_expr IS NOT NORMALIZED %prec IS
{
- $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make1($1), @2), @2);
+ $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make1($1),
+ COERCE_SQL_SYNTAX,
+ @2),
+ @2);
}
| a_expr IS NOT unicode_normal_form NORMALIZED %prec IS
{
- $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make2($1,
makeStringConst($4,@4)), @2), @2);
+ $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make2($1, makeStringConst($4, @4)),
+ COERCE_SQL_SYNTAX,
+ @2),
+ @2);
}
| DEFAULT
{
@@ -13613,31 +13637,41 @@ c_expr: columnref { $$ = $1; }
func_application: func_name '(' ')'
{
- $$ = (Node *) makeFuncCall($1, NIL, @1);
+ $$ = (Node *) makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| func_name '(' func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $3, @1);
+ FuncCall *n = makeFuncCall($1, $3,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $4;
$$ = (Node *)n;
}
| func_name '(' VARIADIC func_arg_expr opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, list_make1($4), @1);
+ FuncCall *n = makeFuncCall($1, list_make1($4),
+ COERCE_EXPLICIT_CALL,
+ @1);
n->func_variadic = true;
n->agg_order = $5;
$$ = (Node *)n;
}
| func_name '(' func_arg_list ',' VARIADIC func_arg_expr opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, lappend($3, $6), @1);
+ FuncCall *n = makeFuncCall($1, lappend($3, $6),
+ COERCE_EXPLICIT_CALL,
+ @1);
n->func_variadic = true;
n->agg_order = $7;
$$ = (Node *)n;
}
| func_name '(' ALL func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $4, @1);
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $5;
/* Ideally we'd mark the FuncCall node to indicate
* "must be an aggregate", but there's no provision
@@ -13647,7 +13681,9 @@ func_application: func_name '(' ')'
}
| func_name '(' DISTINCT func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $4, @1);
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $5;
n->agg_distinct = true;
$$ = (Node *)n;
@@ -13664,7 +13700,9 @@ func_application: func_name '(' ')'
* so that later processing can detect what the argument
* really was.
*/
- FuncCall *n = makeFuncCall($1, NIL, @1);
+ FuncCall *n = makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_star = true;
$$ = (Node *)n;
}
@@ -13738,6 +13776,7 @@ func_expr_common_subexpr:
{
$$ = (Node *) makeFuncCall(SystemFuncName("pg_collation_for"),
list_make1($4),
+ COERCE_SQL_SYNTAX,
@1);
}
| CURRENT_DATE
@@ -13804,31 +13843,49 @@ func_expr_common_subexpr:
{ $$ = makeTypeCast($3, $5, @1); }
| EXTRACT '(' extract_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("date_part"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("date_part"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NORMALIZE '(' a_expr ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("normalize"), list_make1($3), @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("normalize"),
+ list_make1($3),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NORMALIZE '(' a_expr ',' unicode_normal_form ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("normalize"), list_make2($3, makeStringConst($5, @5)),
@1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("normalize"),
+ list_make2($3, makeStringConst($5, @5)),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| OVERLAY '(' overlay_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("overlay"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("overlay"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| POSITION '(' position_list ')'
{
/* position(A in B) is converted to position(B, A) */
- $$ = (Node *) makeFuncCall(SystemFuncName("position"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("position"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| SUBSTRING '(' substr_list ')'
{
/* substring(A from B for C) is converted to
* substring(A, B, C) - thomas 2000-11-28
*/
- $$ = (Node *) makeFuncCall(SystemFuncName("substring"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("substring"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TREAT '(' a_expr AS Typename ')'
{
@@ -13841,28 +13898,41 @@ func_expr_common_subexpr:
* Convert SystemTypeName() to SystemFuncName() even though
* at the moment they result in the same thing.
*/
- $$ = (Node *) makeFuncCall(SystemFuncName(((Value *)llast($5->names))->val.str),
- list_make1($3),
- @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName(((Value *) llast($5->names))->val.str),
+ list_make1($3),
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| TRIM '(' BOTH trim_list ')'
{
/* various trim expressions are defined in SQL
* - thomas 1997-07-19
*/
- $$ = (Node *) makeFuncCall(SystemFuncName("btrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("btrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' LEADING trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("ltrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("ltrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' TRAILING trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("rtrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("rtrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("btrim"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("btrim"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NULLIF '(' a_expr ',' a_expr ')'
{
@@ -13915,7 +13985,10 @@ func_expr_common_subexpr:
{
/* xmlexists(A PASSING [BY REF] B [BY REF]) is
* converted to xmlexists(A, B)*/
- $$ = (Node *) makeFuncCall(SystemFuncName("xmlexists"), list_make2($3, $4), @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("xmlexists"),
+ list_make2($3, $4),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| XMLFOREST '(' xml_attribute_list ')'
{
@@ -14445,10 +14518,10 @@ extract_arg:
;
unicode_normal_form:
- NFC { $$ = "nfc"; }
- | NFD { $$ = "nfd"; }
- | NFKC { $$ = "nfkc"; }
- | NFKD { $$ = "nfkd"; }
+ NFC { $$ = "NFC"; }
+ | NFD { $$ = "NFD"; }
+ | NFKC { $$ = "NFKC"; }
+ | NFKD { $$ = "NFKD"; }
;
/* OVERLAY() arguments */
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 7460e61160..ea4a1f5aeb 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -541,10 +541,11 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
list_length(fc->args) > 1 &&
fc->agg_order == NIL &&
fc->agg_filter == NULL &&
+ fc->over == NULL &&
!fc->agg_star &&
!fc->agg_distinct &&
!fc->func_variadic &&
- fc->over == NULL &&
+ fc->funcformat == COERCE_EXPLICIT_CALL &&
coldeflist == NIL)
{
ListCell *lc;
@@ -558,6 +559,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
newfc = makeFuncCall(SystemFuncName("unnest"),
list_make1(arg),
+ COERCE_EXPLICIT_CALL,
fc->location);
newfexpr = transformExpr(pstate, (Node *) newfc,
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index a7a31704fb..8b4e3ca5e1 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -91,11 +91,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool is_column = (fn == NULL);
List *agg_order = (fn ? fn->agg_order : NIL);
Expr *agg_filter = NULL;
+ WindowDef *over = (fn ? fn->over : NULL);
bool agg_within_group = (fn ? fn->agg_within_group : false);
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
- WindowDef *over = (fn ? fn->over : NULL);
+ CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
Oid rettype;
Oid funcid;
@@ -221,6 +222,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
agg_order == NIL && agg_filter == NULL &&
!agg_star && !agg_distinct && over == NULL &&
!func_variadic && argnames == NIL &&
+ funcformat == COERCE_EXPLICIT_CALL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
ISCOMPLEX(actual_arg_types[0])));
@@ -742,7 +744,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
funcexpr->funcresulttype = rettype;
funcexpr->funcretset = retset;
funcexpr->funcvariadic = func_variadic;
- funcexpr->funcformat = COERCE_EXPLICIT_CALL;
+ funcexpr->funcformat = funcformat;
/* funccollid and inputcollid will be set by parse_collate.c */
funcexpr->args = fargs;
funcexpr->location = location;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 015b0538e3..254c0f65c2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -604,6 +604,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
castnode->location = -1;
funccallnode = makeFuncCall(SystemFuncName("nextval"),
list_make1(castnode),
+ COERCE_EXPLICIT_CALL,
-1);
constraint = makeNode(Constraint);
constraint->contype = CONSTR_DEFAULT;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 28f56074c0..1b2a2d59f0 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -443,6 +443,7 @@ static void get_agg_expr(Aggref *aggref, deparse_context *context,
static void get_agg_combine_expr(Node *node, deparse_context *context,
void *callback_arg);
static void get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context);
+static bool get_func_sql_syntax(FuncExpr *expr, deparse_context *context);
static void get_coercion_expr(Node *arg, deparse_context *context,
Oid resulttype, int32 resulttypmod,
Node *parentNode);
@@ -9155,7 +9156,8 @@ looks_like_function(Node *node)
{
case T_FuncExpr:
/* OK, unless it's going to deparse as a cast */
- return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL);
+ return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL ||
+ ((FuncExpr *) node)->funcformat == COERCE_SQL_SYNTAX);
case T_NullIfExpr:
case T_CoalesceExpr:
case T_MinMaxExpr:
@@ -9257,6 +9259,17 @@ get_func_expr(FuncExpr *expr, deparse_context *context,
return;
}
+ /*
+ * If the function was called using one of the SQL spec's random special
+ * syntaxes, try to reproduce that. If we don't recognize the function,
+ * fall through.
+ */
+ if (expr->funcformat == COERCE_SQL_SYNTAX)
+ {
+ if (get_func_sql_syntax(expr, context))
+ return;
+ }
+
/*
* Normal function: display as proname(args). First we need to extract
* the argument datatypes.
@@ -9492,6 +9505,224 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
}
}
+/*
+ * get_func_sql_syntax - Parse back a SQL-syntax function call
+ *
+ * Returns true if we successfully deparsed, false if we did not
+ * recognize the function.
+ */
+static bool
+get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ Oid funcoid = expr->funcid;
+
+ switch (funcoid)
+ {
+ case F_TIMEZONE_INTERVAL_TIMESTAMP:
+ case F_TIMEZONE_INTERVAL_TIMESTAMPTZ:
+ case F_TIMEZONE_INTERVAL_TIMETZ:
+ case F_TIMEZONE_TEXT_TIMESTAMP:
+ case F_TIMEZONE_TEXT_TIMESTAMPTZ:
+ case F_TIMEZONE_TEXT_TIMETZ:
+ /* AT TIME ZONE ... note reversed argument order */
+ appendStringInfoChar(buf, '(');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " AT TIME ZONE ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:
+ case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:
+ case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:
+ case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ:
+ case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_INTERVAL:
+ case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_TIMESTAMP:
+ case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_INTERVAL:
+ case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_TIMESTAMP:
+ case F_OVERLAPS_TIMETZ_TIMETZ_TIMETZ_TIMETZ:
+ case F_OVERLAPS_TIME_INTERVAL_TIME_INTERVAL:
+ case F_OVERLAPS_TIME_INTERVAL_TIME_TIME:
+ case F_OVERLAPS_TIME_TIME_TIME_INTERVAL:
+ case F_OVERLAPS_TIME_TIME_TIME_TIME:
+ /* (x1, x2) OVERLAPS (y1, y2) */
+ appendStringInfoString(buf, "((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, ") OVERLAPS (");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) lfourth(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+
+ case F_IS_NORMALIZED:
+ /* IS xxx NORMALIZED */
+ appendStringInfoString(buf, "((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ") IS");
+ if (list_length(expr->args) == 2)
+ {
+ Const *con = (Const *) lsecond(expr->args);
+
+ Assert(IsA(con, Const) &&
+ con->consttype == TEXTOID &&
+ !con->constisnull);
+ appendStringInfo(buf, " %s",
+ TextDatumGetCString(con->constvalue));
+ }
+ appendStringInfoString(buf, " NORMALIZED)");
+ return true;
+
+ case F_PG_COLLATION_FOR:
+ /* COLLATION FOR */
+ appendStringInfoString(buf, "COLLATION FOR (");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ /*
+ * XXX EXTRACT, a/k/a date_part(), is intentionally not covered
+ * yet. Add it after we change the return type to numeric.
+ */
+
+ case F_NORMALIZE:
+ /* NORMALIZE() */
+ appendStringInfoString(buf, "NORMALIZE(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ if (list_length(expr->args) == 2)
+ {
+ Const *con = (Const *) lsecond(expr->args);
+
+ Assert(IsA(con, Const) &&
+ con->consttype == TEXTOID &&
+ !con->constisnull);
+ appendStringInfo(buf, ", %s",
+ TextDatumGetCString(con->constvalue));
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_OVERLAY_BIT_BIT_INT4:
+ case F_OVERLAY_BIT_BIT_INT4_INT4:
+ case F_OVERLAY_BYTEA_BYTEA_INT4:
+ case F_OVERLAY_BYTEA_BYTEA_INT4_INT4:
+ case F_OVERLAY_TEXT_TEXT_INT4:
+ case F_OVERLAY_TEXT_TEXT_INT4_INT4:
+ /* OVERLAY() */
+ appendStringInfoString(buf, "OVERLAY(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " PLACING ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ if (list_length(expr->args) == 4)
+ {
+ appendStringInfoString(buf, " FOR ");
+ get_rule_expr((Node *) lfourth(expr->args), context, false);
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_POSITION_BIT_BIT:
+ case F_POSITION_BYTEA_BYTEA:
+ case F_POSITION_TEXT_TEXT:
+ /* POSITION() ... extra parens since args are b_expr not a_expr */
+ appendStringInfoString(buf, "POSITION((");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, ") IN (");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+
+ case F_SUBSTRING_BIT_INT4:
+ case F_SUBSTRING_BIT_INT4_INT4:
+ case F_SUBSTRING_BYTEA_INT4:
+ case F_SUBSTRING_BYTEA_INT4_INT4:
+ case F_SUBSTRING_TEXT_INT4:
+ case F_SUBSTRING_TEXT_INT4_INT4:
+ case F_SUBSTRING_TEXT_TEXT:
+ /* SUBSTRING FROM/FOR */
+ appendStringInfoString(buf, "SUBSTRING(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ if (list_length(expr->args) == 3)
+ {
+ appendStringInfoString(buf, " FOR ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_SUBSTRING_TEXT_TEXT_TEXT:
+ /* SUBSTRING SIMILAR/ESCAPE */
+ appendStringInfoString(buf, "SUBSTRING(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " SIMILAR ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " ESCAPE ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_BTRIM_BYTEA_BYTEA:
+ case F_BTRIM_TEXT:
+ case F_BTRIM_TEXT_TEXT:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(BOTH");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_LTRIM_TEXT:
+ case F_LTRIM_TEXT_TEXT:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(LEADING");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_RTRIM_TEXT:
+ case F_RTRIM_TEXT_TEXT:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(TRAILING");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_XMLEXISTS:
+ /* XMLEXISTS ... extra parens because args are c_expr */
+ appendStringInfoString(buf, "XMLEXISTS((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ") PASSING (");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+ }
+ return false;
+}
+
/* ----------
* get_coercion_expr
*
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 31d9aedeeb..7ebd794713 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -79,7 +79,8 @@ extern ColumnDef *makeColumnDef(const char *colname,
extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype, List *args,
Oid funccollid, Oid inputcollid, CoercionForm fformat);
-extern FuncCall *makeFuncCall(List *name, List *args, int location);
+extern FuncCall *makeFuncCall(List *name, List *args,
+ CoercionForm funcformat, int location);
extern Expr *make_opclause(Oid opno, Oid opresulttype, bool opretset,
Expr *leftop, Expr *rightop,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e1aeea2560..80e2aba369 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -353,11 +353,12 @@ typedef struct FuncCall
List *args; /* the arguments (list of exprs) */
List *agg_order; /* ORDER BY (list of SortBy) */
Node *agg_filter; /* FILTER clause, if any */
+ struct WindowDef *over; /* OVER clause, if any */
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
- struct WindowDef *over; /* OVER clause, if any */
+ CoercionForm funcformat; /* how to display this node */
int location; /* token location, or -1 if unknown */
} FuncCall;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index fd65ee8f9c..5b190bb99b 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -445,7 +445,10 @@ typedef enum CoercionContext
} CoercionContext;
/*
- * CoercionForm - how to display a node that could have come from a cast
+ * CoercionForm - how to display a FuncExpr or related node
+ *
+ * "Coercion" is a bit of a misnomer, since this value records other
+ * special syntaxes besides casts, but for now we'll keep this naming.
*
* NB: equal() ignores CoercionForm fields, therefore this *must* not carry
* any semantically significant information. We need that behavior so that
@@ -457,7 +460,8 @@ typedef enum CoercionForm
{
COERCE_EXPLICIT_CALL, /* display as a function call */
COERCE_EXPLICIT_CAST, /* display as an explicit cast */
- COERCE_IMPLICIT_CAST /* implicit cast, so hide it */
+ COERCE_IMPLICIT_CAST, /* implicit cast, so hide it */
+ COERCE_SQL_SYNTAX /* display with SQL-mandated special syntax */
} CoercionForm;
/*
diff --git a/src/test/modules/test_rls_hooks/test_rls_hooks.c b/src/test/modules/test_rls_hooks/test_rls_hooks.c
index 0bfa878a25..c0aaabdcdb 100644
--- a/src/test/modules/test_rls_hooks/test_rls_hooks.c
+++ b/src/test/modules/test_rls_hooks/test_rls_hooks.c
@@ -95,7 +95,10 @@ test_rls_hooks_permissive(CmdType cmdtype, Relation relation)
*/
n = makeFuncCall(list_make2(makeString("pg_catalog"),
- makeString("current_user")), NIL, 0);
+ makeString("current_user")),
+ NIL,
+ COERCE_EXPLICIT_CALL,
+ -1);
c = makeNode(ColumnRef);
c->fields = list_make1(makeString("username"));
@@ -155,7 +158,10 @@ test_rls_hooks_restrictive(CmdType cmdtype, Relation relation)
policy->roles = construct_array(&role, 1, OIDOID, sizeof(Oid), true, TYPALIGN_INT);
n = makeFuncCall(list_make2(makeString("pg_catalog"),
- makeString("current_user")), NIL, 0);
+ makeString("current_user")),
+ NIL,
+ COERCE_EXPLICIT_CALL,
+ -1);
c = makeNode(ColumnRef);
c->fields = list_make1(makeString("supervisor"));
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f10a3a7a12..465a0a4da1 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1710,13 +1710,50 @@ select pg_get_viewdef('tt20v', true);
i4.i4, +
i8.i8 +
FROM COALESCE(1, 2) c(c), +
- pg_collation_for('x'::text) col(col), +
+ COLLATION FOR ('x'::text) col(col), +
CURRENT_DATE d(d), +
LOCALTIMESTAMP(3) t(t), +
CAST(1 + 2 AS integer) i4(i4), +
CAST((1 + 2)::bigint AS bigint) i8(i8);
(1 row)
+-- reverse-listing of various special function syntaxes required by SQL
+create view tt201v as
+select
+ extract(day from now()) as extr,
+ (now(), '1 day'::interval) overlaps
+ (current_timestamp(2), '1 day'::interval) as o,
+ 'foo' is normalized isn,
+ 'foo' is nfkc normalized isnn,
+ normalize('foo') as n,
+ normalize('foo', nfkd) as nfkd,
+ overlay('foo' placing 'bar' from 2) as ovl,
+ overlay('foo' placing 'bar' from 2 for 3) as ovl2,
+ position('foo' in 'foobar') as p,
+ substring('foo' from 2 for 3) as s,
+ substring('foo' similar 'f' escape '#') as ss,
+ trim(' ' from ' foo ') as bt,
+ trim(leading ' ' from ' foo ') as lt,
+ trim(trailing ' foo ') as rt;
+select pg_get_viewdef('tt201v', true);
+ pg_get_viewdef
+-----------------------------------------------------------------------------------------------
+ SELECT date_part('day'::text, now()) AS extr, +
+ ((now(), '@ 1 day'::interval) OVERLAPS (CURRENT_TIMESTAMP(2), '@ 1 day'::interval)) AS o,+
+ (('foo'::text) IS NORMALIZED) AS isn, +
+ (('foo'::text) IS NFKC NORMALIZED) AS isnn, +
+ NORMALIZE('foo'::text) AS n, +
+ NORMALIZE('foo'::text, NFKD) AS nfkd, +
+ OVERLAY('foo'::text PLACING 'bar'::text FROM 2) AS ovl, +
+ OVERLAY('foo'::text PLACING 'bar'::text FROM 2 FOR 3) AS ovl2, +
+ POSITION(('foo'::text) IN ('foobar'::text)) AS p, +
+ SUBSTRING('foo'::text FROM 2 FOR 3) AS s, +
+ SUBSTRING('foo'::text SIMILAR 'f'::text ESCAPE '#'::text) AS ss, +
+ TRIM(BOTH ' '::text FROM ' foo '::text) AS bt, +
+ TRIM(LEADING ' '::text FROM ' foo '::text) AS lt, +
+ TRIM(TRAILING FROM ' foo '::text) AS rt;
+(1 row)
+
-- corner cases with empty join conditions
create view tt21v as
select * from tt5 natural inner join tt6;
@@ -1904,7 +1941,7 @@ drop cascades to view aliased_view_2
drop cascades to view aliased_view_3
drop cascades to view aliased_view_4
DROP SCHEMA testviewschm2 CASCADE;
-NOTICE: drop cascades to 67 other objects
+NOTICE: drop cascades to 68 other objects
DETAIL: drop cascades to table t1
drop cascades to view temporal1
drop cascades to view temporal2
@@ -1966,6 +2003,7 @@ drop cascades to view tt17v
drop cascades to view tt18v
drop cascades to view tt19v
drop cascades to view tt20v
+drop cascades to view tt201v
drop cascades to view tt21v
drop cascades to view tt22v
drop cascades to view tt23v
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 639b50308e..c300965554 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2726,10 +2726,10 @@ create temp table tmptz (f1 timestamptz primary key);
insert into tmptz values ('2017-01-18 00:00+00');
explain (costs off)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on tmptz
- Filter: (timezone('utc'::text, f1) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
+ Filter: ((f1 AT TIME ZONE 'utc'::text) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
(2 rows)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index e7af0bf2fa..62d1586ea0 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -586,6 +586,27 @@ select * from
cast(1+2 as int8) as i8;
select pg_get_viewdef('tt20v', true);
+-- reverse-listing of various special function syntaxes required by SQL
+
+create view tt201v as
+select
+ extract(day from now()) as extr,
+ (now(), '1 day'::interval) overlaps
+ (current_timestamp(2), '1 day'::interval) as o,
+ 'foo' is normalized isn,
+ 'foo' is nfkc normalized isnn,
+ normalize('foo') as n,
+ normalize('foo', nfkd) as nfkd,
+ overlay('foo' placing 'bar' from 2) as ovl,
+ overlay('foo' placing 'bar' from 2 for 3) as ovl2,
+ position('foo' in 'foobar') as p,
+ substring('foo' from 2 for 3) as s,
+ substring('foo' similar 'f' escape '#') as ss,
+ trim(' ' from ' foo ') as bt,
+ trim(leading ' ' from ' foo ') as lt,
+ trim(trailing ' foo ') as rt;
+select pg_get_viewdef('tt201v', true);
+
-- corner cases with empty join conditions
create view tt21v as
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
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
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 530aac68a7..3031c52991 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2682,11 +2682,12 @@ _copyFuncCall(const FuncCall *from)
COPY_NODE_FIELD(args);
COPY_NODE_FIELD(agg_order);
COPY_NODE_FIELD(agg_filter);
+ COPY_NODE_FIELD(over);
COPY_SCALAR_FIELD(agg_within_group);
COPY_SCALAR_FIELD(agg_star);
COPY_SCALAR_FIELD(agg_distinct);
COPY_SCALAR_FIELD(func_variadic);
- COPY_NODE_FIELD(over);
+ COPY_SCALAR_FIELD(funcformat);
COPY_LOCATION_FIELD(location);
return newnode;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 0cf90ef33c..9aa853748d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2369,11 +2369,12 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b)
COMPARE_NODE_FIELD(args);
COMPARE_NODE_FIELD(agg_order);
COMPARE_NODE_FIELD(agg_filter);
+ COMPARE_NODE_FIELD(over);
COMPARE_SCALAR_FIELD(agg_within_group);
COMPARE_SCALAR_FIELD(agg_star);
COMPARE_SCALAR_FIELD(agg_distinct);
COMPARE_SCALAR_FIELD(func_variadic);
- COMPARE_NODE_FIELD(over);
+ COMPARE_SCALAR_FIELD(funcformat);
COMPARE_LOCATION_FIELD(location);
return true;
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 49de285f01..ee033ae779 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -582,7 +582,7 @@ makeDefElemExtended(char *nameSpace, char *name, Node *arg,
* supply. Any non-default parameters have to be inserted by the caller.
*/
FuncCall *
-makeFuncCall(List *name, List *args, int location)
+makeFuncCall(List *name, List *args, CoercionForm funcformat, int location)
{
FuncCall *n = makeNode(FuncCall);
@@ -590,11 +590,12 @@ makeFuncCall(List *name, List *args, int location)
n->args = args;
n->agg_order = NIL;
n->agg_filter = NULL;
+ n->over = NULL;
n->agg_within_group = false;
n->agg_star = false;
n->agg_distinct = false;
n->func_variadic = false;
- n->over = NULL;
+ n->funcformat = funcformat;
n->location = location;
return n;
}
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 7e324c12e2..4504b1503b 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2765,11 +2765,12 @@ _outFuncCall(StringInfo str, const FuncCall *node)
WRITE_NODE_FIELD(args);
WRITE_NODE_FIELD(agg_order);
WRITE_NODE_FIELD(agg_filter);
+ WRITE_NODE_FIELD(over);
WRITE_BOOL_FIELD(agg_within_group);
WRITE_BOOL_FIELD(agg_star);
WRITE_BOOL_FIELD(agg_distinct);
WRITE_BOOL_FIELD(func_variadic);
- WRITE_NODE_FIELD(over);
+ WRITE_ENUM_FIELD(funcformat, CoercionForm);
WRITE_LOCATION_FIELD(location);
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 357ab93fb6..df263e4fdd 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -490,7 +490,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> rowsfrom_item rowsfrom_list opt_col_def_list
%type <boolean> opt_ordinality
%type <list> ExclusionConstraintList ExclusionConstraintElem
-%type <list> func_arg_list
+%type <list> func_arg_list func_arg_list_opt
%type <node> func_arg_expr
%type <list> row explicit_row implicit_row type_list array_expr_list
%type <node> case_expr case_arg when_clause case_default
@@ -12969,6 +12969,7 @@ a_expr: c_expr { $$ = $1; }
{
$$ = (Node *) makeFuncCall(SystemFuncName("timezone"),
list_make2($5, $1),
+ COERCE_SQL_SYNTAX,
@2);
}
/*
@@ -13032,6 +13033,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($3, $5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_LIKE, "~~",
$1, (Node *) n, @2);
@@ -13045,6 +13047,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_LIKE, "!~~",
$1, (Node *) n, @2);
@@ -13058,6 +13061,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($3, $5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_ILIKE, "~~*",
$1, (Node *) n, @2);
@@ -13071,6 +13075,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("like_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_ILIKE, "!~~*",
$1, (Node *) n, @2);
@@ -13080,6 +13085,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make1($4),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "~",
$1, (Node *) n, @2);
@@ -13088,6 +13094,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make2($4, $6),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "~",
$1, (Node *) n, @2);
@@ -13096,6 +13103,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make1($5),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "!~",
$1, (Node *) n, @2);
@@ -13104,6 +13112,7 @@ a_expr: c_expr { $$ = $1; }
{
FuncCall *n = makeFuncCall(SystemFuncName("similar_to_escape"),
list_make2($5, $7),
+ COERCE_EXPLICIT_CALL,
@2);
$$ = (Node *) makeSimpleA_Expr(AEXPR_SIMILAR, "!~",
$1, (Node *) n, @2);
@@ -13164,6 +13173,7 @@ a_expr: c_expr { $$ = $1; }
parser_errposition(@3)));
$$ = (Node *) makeFuncCall(SystemFuncName("overlaps"),
list_concat($1, $3),
+ COERCE_SQL_SYNTAX,
@2);
}
| a_expr IS TRUE_P %prec IS
@@ -13351,19 +13361,33 @@ a_expr: c_expr { $$ = $1; }
}
| a_expr IS NORMALIZED %prec IS
{
- $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make1($1), @2);
+ $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make1($1),
+ COERCE_SQL_SYNTAX,
+ @2);
}
| a_expr IS unicode_normal_form NORMALIZED %prec IS
{
- $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make2($1, makeStringConst($3,
@3)),@2);
+ $$ = (Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make2($1, makeStringConst($3, @3)),
+ COERCE_SQL_SYNTAX,
+ @2);
}
| a_expr IS NOT NORMALIZED %prec IS
{
- $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make1($1), @2), @2);
+ $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make1($1),
+ COERCE_SQL_SYNTAX,
+ @2),
+ @2);
}
| a_expr IS NOT unicode_normal_form NORMALIZED %prec IS
{
- $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"), list_make2($1,
makeStringConst($4,@4)), @2), @2);
+ $$ = makeNotExpr((Node *) makeFuncCall(SystemFuncName("is_normalized"),
+ list_make2($1, makeStringConst($4, @4)),
+ COERCE_SQL_SYNTAX,
+ @2),
+ @2);
}
| DEFAULT
{
@@ -13613,31 +13637,41 @@ c_expr: columnref { $$ = $1; }
func_application: func_name '(' ')'
{
- $$ = (Node *) makeFuncCall($1, NIL, @1);
+ $$ = (Node *) makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| func_name '(' func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $3, @1);
+ FuncCall *n = makeFuncCall($1, $3,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $4;
$$ = (Node *)n;
}
| func_name '(' VARIADIC func_arg_expr opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, list_make1($4), @1);
+ FuncCall *n = makeFuncCall($1, list_make1($4),
+ COERCE_EXPLICIT_CALL,
+ @1);
n->func_variadic = true;
n->agg_order = $5;
$$ = (Node *)n;
}
| func_name '(' func_arg_list ',' VARIADIC func_arg_expr opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, lappend($3, $6), @1);
+ FuncCall *n = makeFuncCall($1, lappend($3, $6),
+ COERCE_EXPLICIT_CALL,
+ @1);
n->func_variadic = true;
n->agg_order = $7;
$$ = (Node *)n;
}
| func_name '(' ALL func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $4, @1);
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $5;
/* Ideally we'd mark the FuncCall node to indicate
* "must be an aggregate", but there's no provision
@@ -13647,7 +13681,9 @@ func_application: func_name '(' ')'
}
| func_name '(' DISTINCT func_arg_list opt_sort_clause ')'
{
- FuncCall *n = makeFuncCall($1, $4, @1);
+ FuncCall *n = makeFuncCall($1, $4,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_order = $5;
n->agg_distinct = true;
$$ = (Node *)n;
@@ -13664,7 +13700,9 @@ func_application: func_name '(' ')'
* so that later processing can detect what the argument
* really was.
*/
- FuncCall *n = makeFuncCall($1, NIL, @1);
+ FuncCall *n = makeFuncCall($1, NIL,
+ COERCE_EXPLICIT_CALL,
+ @1);
n->agg_star = true;
$$ = (Node *)n;
}
@@ -13738,6 +13776,7 @@ func_expr_common_subexpr:
{
$$ = (Node *) makeFuncCall(SystemFuncName("pg_collation_for"),
list_make1($4),
+ COERCE_SQL_SYNTAX,
@1);
}
| CURRENT_DATE
@@ -13804,31 +13843,77 @@ func_expr_common_subexpr:
{ $$ = makeTypeCast($3, $5, @1); }
| EXTRACT '(' extract_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("date_part"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("date_part"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NORMALIZE '(' a_expr ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("normalize"), list_make1($3), @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("normalize"),
+ list_make1($3),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NORMALIZE '(' a_expr ',' unicode_normal_form ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("normalize"), list_make2($3, makeStringConst($5, @5)),
@1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("normalize"),
+ list_make2($3, makeStringConst($5, @5)),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| OVERLAY '(' overlay_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("overlay"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("overlay"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
+ }
+ | OVERLAY '(' func_arg_list_opt ')'
+ {
+ /*
+ * allow functions named overlay() to be called without
+ * special syntax
+ */
+ $$ = (Node *) makeFuncCall(list_make1(makeString("overlay")),
+ $3,
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| POSITION '(' position_list ')'
{
- /* position(A in B) is converted to position(B, A) */
- $$ = (Node *) makeFuncCall(SystemFuncName("position"), $3, @1);
+ /*
+ * position(A in B) is converted to position(B, A)
+ *
+ * We deliberately don't offer a "plain syntax" option
+ * for position(), because the reversal of the arguments
+ * creates too much risk of confusion.
+ */
+ $$ = (Node *) makeFuncCall(SystemFuncName("position"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| SUBSTRING '(' substr_list ')'
{
/* substring(A from B for C) is converted to
* substring(A, B, C) - thomas 2000-11-28
*/
- $$ = (Node *) makeFuncCall(SystemFuncName("substring"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("substring"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
+ }
+ | SUBSTRING '(' func_arg_list_opt ')'
+ {
+ /*
+ * allow functions named substring() to be called without
+ * special syntax
+ */
+ $$ = (Node *) makeFuncCall(list_make1(makeString("substring")),
+ $3,
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| TREAT '(' a_expr AS Typename ')'
{
@@ -13841,28 +13926,41 @@ func_expr_common_subexpr:
* Convert SystemTypeName() to SystemFuncName() even though
* at the moment they result in the same thing.
*/
- $$ = (Node *) makeFuncCall(SystemFuncName(((Value *)llast($5->names))->val.str),
- list_make1($3),
- @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName(((Value *) llast($5->names))->val.str),
+ list_make1($3),
+ COERCE_EXPLICIT_CALL,
+ @1);
}
| TRIM '(' BOTH trim_list ')'
{
/* various trim expressions are defined in SQL
* - thomas 1997-07-19
*/
- $$ = (Node *) makeFuncCall(SystemFuncName("btrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("btrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' LEADING trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("ltrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("ltrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' TRAILING trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("rtrim"), $4, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("rtrim"),
+ $4,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| TRIM '(' trim_list ')'
{
- $$ = (Node *) makeFuncCall(SystemFuncName("btrim"), $3, @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("btrim"),
+ $3,
+ COERCE_SQL_SYNTAX,
+ @1);
}
| NULLIF '(' a_expr ',' a_expr ')'
{
@@ -13915,7 +14013,10 @@ func_expr_common_subexpr:
{
/* xmlexists(A PASSING [BY REF] B [BY REF]) is
* converted to xmlexists(A, B)*/
- $$ = (Node *) makeFuncCall(SystemFuncName("xmlexists"), list_make2($3, $4), @1);
+ $$ = (Node *) makeFuncCall(SystemFuncName("xmlexists"),
+ list_make2($3, $4),
+ COERCE_SQL_SYNTAX,
+ @1);
}
| XMLFOREST '(' xml_attribute_list ')'
{
@@ -14399,6 +14500,10 @@ func_arg_expr: a_expr
}
;
+func_arg_list_opt: func_arg_list { $$ = $1; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
type_list: Typename { $$ = list_make1($1); }
| type_list ',' Typename { $$ = lappend($1, $3); }
;
@@ -14445,10 +14550,10 @@ extract_arg:
;
unicode_normal_form:
- NFC { $$ = "nfc"; }
- | NFD { $$ = "nfd"; }
- | NFKC { $$ = "nfkc"; }
- | NFKD { $$ = "nfkd"; }
+ NFC { $$ = "NFC"; }
+ | NFD { $$ = "NFD"; }
+ | NFKC { $$ = "NFKC"; }
+ | NFKD { $$ = "NFKD"; }
;
/* OVERLAY() arguments */
@@ -14468,29 +14573,24 @@ overlay_list:
/* position_list uses b_expr not a_expr to avoid conflict with general IN */
position_list:
b_expr IN_P b_expr { $$ = list_make2($3, $1); }
- | /*EMPTY*/ { $$ = NIL; }
;
/*
* SUBSTRING() arguments
*
* Note that SQL:1999 has both
- *
* text FROM int FOR int
- *
* and
- *
* text FROM pattern FOR escape
*
* In the parser we map them both to a call to the substring() function and
* rely on type resolution to pick the right one.
*
* In SQL:2003, the second variant was changed to
- *
* text SIMILAR pattern ESCAPE escape
- *
* We could in theory map that to a different function internally, but
- * since we still support the SQL:1999 version, we don't.
+ * since we still support the SQL:1999 version, we don't. However,
+ * ruleutils.c will reverse-list the call in the newer style.
*/
substr_list:
a_expr FROM a_expr FOR a_expr
@@ -14504,6 +14604,13 @@ substr_list:
}
| a_expr FROM a_expr
{
+ /*
+ * Because we aren't restricting data types here, this
+ * syntax can end up resolving to textregexsubstr().
+ * We've historically allowed that to happen, so continue
+ * to accept it. However, ruleutils.c will reverse-list
+ * such a call in regular function call syntax.
+ */
$$ = list_make2($1, $3);
}
| a_expr FOR a_expr
@@ -14527,16 +14634,6 @@ substr_list:
{
$$ = list_make3($1, $3, $5);
}
- /*
- * We also want to support generic substring functions that
- * accept the usual generic list of arguments.
- */
- | expr_list
- {
- $$ = $1;
- }
- | /*EMPTY*/
- { $$ = NIL; }
;
trim_list: a_expr FROM expr_list { $$ = lappend($3, $1); }
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 7460e61160..ea4a1f5aeb 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -541,10 +541,11 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
list_length(fc->args) > 1 &&
fc->agg_order == NIL &&
fc->agg_filter == NULL &&
+ fc->over == NULL &&
!fc->agg_star &&
!fc->agg_distinct &&
!fc->func_variadic &&
- fc->over == NULL &&
+ fc->funcformat == COERCE_EXPLICIT_CALL &&
coldeflist == NIL)
{
ListCell *lc;
@@ -558,6 +559,7 @@ transformRangeFunction(ParseState *pstate, RangeFunction *r)
newfc = makeFuncCall(SystemFuncName("unnest"),
list_make1(arg),
+ COERCE_EXPLICIT_CALL,
fc->location);
newfexpr = transformExpr(pstate, (Node *) newfc,
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index a7a31704fb..8b4e3ca5e1 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -91,11 +91,12 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
bool is_column = (fn == NULL);
List *agg_order = (fn ? fn->agg_order : NIL);
Expr *agg_filter = NULL;
+ WindowDef *over = (fn ? fn->over : NULL);
bool agg_within_group = (fn ? fn->agg_within_group : false);
bool agg_star = (fn ? fn->agg_star : false);
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
- WindowDef *over = (fn ? fn->over : NULL);
+ CoercionForm funcformat = (fn ? fn->funcformat : COERCE_EXPLICIT_CALL);
bool could_be_projection;
Oid rettype;
Oid funcid;
@@ -221,6 +222,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
agg_order == NIL && agg_filter == NULL &&
!agg_star && !agg_distinct && over == NULL &&
!func_variadic && argnames == NIL &&
+ funcformat == COERCE_EXPLICIT_CALL &&
list_length(funcname) == 1 &&
(actual_arg_types[0] == RECORDOID ||
ISCOMPLEX(actual_arg_types[0])));
@@ -742,7 +744,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
funcexpr->funcresulttype = rettype;
funcexpr->funcretset = retset;
funcexpr->funcvariadic = func_variadic;
- funcexpr->funcformat = COERCE_EXPLICIT_CALL;
+ funcexpr->funcformat = funcformat;
/* funccollid and inputcollid will be set by parse_collate.c */
funcexpr->args = fargs;
funcexpr->location = location;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 015b0538e3..254c0f65c2 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -604,6 +604,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
castnode->location = -1;
funccallnode = makeFuncCall(SystemFuncName("nextval"),
list_make1(castnode),
+ COERCE_EXPLICIT_CALL,
-1);
constraint = makeNode(Constraint);
constraint->contype = CONSTR_DEFAULT;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 28f56074c0..3fabcca82f 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -443,6 +443,7 @@ static void get_agg_expr(Aggref *aggref, deparse_context *context,
static void get_agg_combine_expr(Node *node, deparse_context *context,
void *callback_arg);
static void get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context);
+static bool get_func_sql_syntax(FuncExpr *expr, deparse_context *context);
static void get_coercion_expr(Node *arg, deparse_context *context,
Oid resulttype, int32 resulttypmod,
Node *parentNode);
@@ -9155,7 +9156,8 @@ looks_like_function(Node *node)
{
case T_FuncExpr:
/* OK, unless it's going to deparse as a cast */
- return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL);
+ return (((FuncExpr *) node)->funcformat == COERCE_EXPLICIT_CALL ||
+ ((FuncExpr *) node)->funcformat == COERCE_SQL_SYNTAX);
case T_NullIfExpr:
case T_CoalesceExpr:
case T_MinMaxExpr:
@@ -9257,6 +9259,17 @@ get_func_expr(FuncExpr *expr, deparse_context *context,
return;
}
+ /*
+ * If the function was called using one of the SQL spec's random special
+ * syntaxes, try to reproduce that. If we don't recognize the function,
+ * fall through.
+ */
+ if (expr->funcformat == COERCE_SQL_SYNTAX)
+ {
+ if (get_func_sql_syntax(expr, context))
+ return;
+ }
+
/*
* Normal function: display as proname(args). First we need to extract
* the argument datatypes.
@@ -9492,6 +9505,223 @@ get_windowfunc_expr(WindowFunc *wfunc, deparse_context *context)
}
}
+/*
+ * get_func_sql_syntax - Parse back a SQL-syntax function call
+ *
+ * Returns true if we successfully deparsed, false if we did not
+ * recognize the function.
+ */
+static bool
+get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
+{
+ StringInfo buf = context->buf;
+ Oid funcoid = expr->funcid;
+
+ switch (funcoid)
+ {
+ case F_TIMEZONE_INTERVAL_TIMESTAMP:
+ case F_TIMEZONE_INTERVAL_TIMESTAMPTZ:
+ case F_TIMEZONE_INTERVAL_TIMETZ:
+ case F_TIMEZONE_TEXT_TIMESTAMP:
+ case F_TIMEZONE_TEXT_TIMESTAMPTZ:
+ case F_TIMEZONE_TEXT_TIMETZ:
+ /* AT TIME ZONE ... note reversed argument order */
+ appendStringInfoChar(buf, '(');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " AT TIME ZONE ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_INTERVAL:
+ case F_OVERLAPS_TIMESTAMPTZ_INTERVAL_TIMESTAMPTZ_TIMESTAMPTZ:
+ case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_INTERVAL:
+ case F_OVERLAPS_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ_TIMESTAMPTZ:
+ case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_INTERVAL:
+ case F_OVERLAPS_TIMESTAMP_INTERVAL_TIMESTAMP_TIMESTAMP:
+ case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_INTERVAL:
+ case F_OVERLAPS_TIMESTAMP_TIMESTAMP_TIMESTAMP_TIMESTAMP:
+ case F_OVERLAPS_TIMETZ_TIMETZ_TIMETZ_TIMETZ:
+ case F_OVERLAPS_TIME_INTERVAL_TIME_INTERVAL:
+ case F_OVERLAPS_TIME_INTERVAL_TIME_TIME:
+ case F_OVERLAPS_TIME_TIME_TIME_INTERVAL:
+ case F_OVERLAPS_TIME_TIME_TIME_TIME:
+ /* (x1, x2) OVERLAPS (y1, y2) */
+ appendStringInfoString(buf, "((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, ") OVERLAPS (");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) lfourth(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+
+ case F_IS_NORMALIZED:
+ /* IS xxx NORMALIZED */
+ appendStringInfoString(buf, "((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ") IS");
+ if (list_length(expr->args) == 2)
+ {
+ Const *con = (Const *) lsecond(expr->args);
+
+ Assert(IsA(con, Const) &&
+ con->consttype == TEXTOID &&
+ !con->constisnull);
+ appendStringInfo(buf, " %s",
+ TextDatumGetCString(con->constvalue));
+ }
+ appendStringInfoString(buf, " NORMALIZED)");
+ return true;
+
+ case F_PG_COLLATION_FOR:
+ /* COLLATION FOR */
+ appendStringInfoString(buf, "COLLATION FOR (");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ /*
+ * XXX EXTRACT, a/k/a date_part(), is intentionally not covered
+ * yet. Add it after we change the return type to numeric.
+ */
+
+ case F_NORMALIZE:
+ /* NORMALIZE() */
+ appendStringInfoString(buf, "NORMALIZE(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ if (list_length(expr->args) == 2)
+ {
+ Const *con = (Const *) lsecond(expr->args);
+
+ Assert(IsA(con, Const) &&
+ con->consttype == TEXTOID &&
+ !con->constisnull);
+ appendStringInfo(buf, ", %s",
+ TextDatumGetCString(con->constvalue));
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_OVERLAY_BIT_BIT_INT4:
+ case F_OVERLAY_BIT_BIT_INT4_INT4:
+ case F_OVERLAY_BYTEA_BYTEA_INT4:
+ case F_OVERLAY_BYTEA_BYTEA_INT4_INT4:
+ case F_OVERLAY_TEXT_TEXT_INT4:
+ case F_OVERLAY_TEXT_TEXT_INT4_INT4:
+ /* OVERLAY() */
+ appendStringInfoString(buf, "OVERLAY(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " PLACING ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ if (list_length(expr->args) == 4)
+ {
+ appendStringInfoString(buf, " FOR ");
+ get_rule_expr((Node *) lfourth(expr->args), context, false);
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_POSITION_BIT_BIT:
+ case F_POSITION_BYTEA_BYTEA:
+ case F_POSITION_TEXT_TEXT:
+ /* POSITION() ... extra parens since args are b_expr not a_expr */
+ appendStringInfoString(buf, "POSITION((");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, ") IN (");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+
+ case F_SUBSTRING_BIT_INT4:
+ case F_SUBSTRING_BIT_INT4_INT4:
+ case F_SUBSTRING_BYTEA_INT4:
+ case F_SUBSTRING_BYTEA_INT4_INT4:
+ case F_SUBSTRING_TEXT_INT4:
+ case F_SUBSTRING_TEXT_INT4_INT4:
+ /* SUBSTRING FROM/FOR (i.e., integer-position variants) */
+ appendStringInfoString(buf, "SUBSTRING(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ if (list_length(expr->args) == 3)
+ {
+ appendStringInfoString(buf, " FOR ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ }
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_SUBSTRING_TEXT_TEXT_TEXT:
+ /* SUBSTRING SIMILAR/ESCAPE */
+ appendStringInfoString(buf, "SUBSTRING(");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, " SIMILAR ");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, " ESCAPE ");
+ get_rule_expr((Node *) lthird(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_BTRIM_BYTEA_BYTEA:
+ case F_BTRIM_TEXT:
+ case F_BTRIM_TEXT_TEXT:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(BOTH");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_LTRIM_TEXT:
+ case F_LTRIM_TEXT_TEXT:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(LEADING");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_RTRIM_TEXT:
+ case F_RTRIM_TEXT_TEXT:
+ /* TRIM() */
+ appendStringInfoString(buf, "TRIM(TRAILING");
+ if (list_length(expr->args) == 2)
+ {
+ appendStringInfoChar(buf, ' ');
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ }
+ appendStringInfoString(buf, " FROM ");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoChar(buf, ')');
+ return true;
+
+ case F_XMLEXISTS:
+ /* XMLEXISTS ... extra parens because args are c_expr */
+ appendStringInfoString(buf, "XMLEXISTS((");
+ get_rule_expr((Node *) linitial(expr->args), context, false);
+ appendStringInfoString(buf, ") PASSING (");
+ get_rule_expr((Node *) lsecond(expr->args), context, false);
+ appendStringInfoString(buf, "))");
+ return true;
+ }
+ return false;
+}
+
/* ----------
* get_coercion_expr
*
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 31d9aedeeb..7ebd794713 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -79,7 +79,8 @@ extern ColumnDef *makeColumnDef(const char *colname,
extern FuncExpr *makeFuncExpr(Oid funcid, Oid rettype, List *args,
Oid funccollid, Oid inputcollid, CoercionForm fformat);
-extern FuncCall *makeFuncCall(List *name, List *args, int location);
+extern FuncCall *makeFuncCall(List *name, List *args,
+ CoercionForm funcformat, int location);
extern Expr *make_opclause(Oid opno, Oid opresulttype, bool opretset,
Expr *leftop, Expr *rightop,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e1aeea2560..80e2aba369 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -353,11 +353,12 @@ typedef struct FuncCall
List *args; /* the arguments (list of exprs) */
List *agg_order; /* ORDER BY (list of SortBy) */
Node *agg_filter; /* FILTER clause, if any */
+ struct WindowDef *over; /* OVER clause, if any */
bool agg_within_group; /* ORDER BY appeared in WITHIN GROUP */
bool agg_star; /* argument was really '*' */
bool agg_distinct; /* arguments were labeled DISTINCT */
bool func_variadic; /* last argument was labeled VARIADIC */
- struct WindowDef *over; /* OVER clause, if any */
+ CoercionForm funcformat; /* how to display this node */
int location; /* token location, or -1 if unknown */
} FuncCall;
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index fd65ee8f9c..5b190bb99b 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -445,7 +445,10 @@ typedef enum CoercionContext
} CoercionContext;
/*
- * CoercionForm - how to display a node that could have come from a cast
+ * CoercionForm - how to display a FuncExpr or related node
+ *
+ * "Coercion" is a bit of a misnomer, since this value records other
+ * special syntaxes besides casts, but for now we'll keep this naming.
*
* NB: equal() ignores CoercionForm fields, therefore this *must* not carry
* any semantically significant information. We need that behavior so that
@@ -457,7 +460,8 @@ typedef enum CoercionForm
{
COERCE_EXPLICIT_CALL, /* display as a function call */
COERCE_EXPLICIT_CAST, /* display as an explicit cast */
- COERCE_IMPLICIT_CAST /* implicit cast, so hide it */
+ COERCE_IMPLICIT_CAST, /* implicit cast, so hide it */
+ COERCE_SQL_SYNTAX /* display with SQL-mandated special syntax */
} CoercionForm;
/*
diff --git a/src/test/modules/test_rls_hooks/test_rls_hooks.c b/src/test/modules/test_rls_hooks/test_rls_hooks.c
index 0bfa878a25..c0aaabdcdb 100644
--- a/src/test/modules/test_rls_hooks/test_rls_hooks.c
+++ b/src/test/modules/test_rls_hooks/test_rls_hooks.c
@@ -95,7 +95,10 @@ test_rls_hooks_permissive(CmdType cmdtype, Relation relation)
*/
n = makeFuncCall(list_make2(makeString("pg_catalog"),
- makeString("current_user")), NIL, 0);
+ makeString("current_user")),
+ NIL,
+ COERCE_EXPLICIT_CALL,
+ -1);
c = makeNode(ColumnRef);
c->fields = list_make1(makeString("username"));
@@ -155,7 +158,10 @@ test_rls_hooks_restrictive(CmdType cmdtype, Relation relation)
policy->roles = construct_array(&role, 1, OIDOID, sizeof(Oid), true, TYPALIGN_INT);
n = makeFuncCall(list_make2(makeString("pg_catalog"),
- makeString("current_user")), NIL, 0);
+ makeString("current_user")),
+ NIL,
+ COERCE_EXPLICIT_CALL,
+ -1);
c = makeNode(ColumnRef);
c->fields = list_make1(makeString("supervisor"));
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f10a3a7a12..b234d2d4f9 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -1710,13 +1710,52 @@ select pg_get_viewdef('tt20v', true);
i4.i4, +
i8.i8 +
FROM COALESCE(1, 2) c(c), +
- pg_collation_for('x'::text) col(col), +
+ COLLATION FOR ('x'::text) col(col), +
CURRENT_DATE d(d), +
LOCALTIMESTAMP(3) t(t), +
CAST(1 + 2 AS integer) i4(i4), +
CAST((1 + 2)::bigint AS bigint) i8(i8);
(1 row)
+-- reverse-listing of various special function syntaxes required by SQL
+create view tt201v as
+select
+ extract(day from now()) as extr,
+ (now(), '1 day'::interval) overlaps
+ (current_timestamp(2), '1 day'::interval) as o,
+ 'foo' is normalized isn,
+ 'foo' is nfkc normalized isnn,
+ normalize('foo') as n,
+ normalize('foo', nfkd) as nfkd,
+ overlay('foo' placing 'bar' from 2) as ovl,
+ overlay('foo' placing 'bar' from 2 for 3) as ovl2,
+ position('foo' in 'foobar') as p,
+ substring('foo' from 2 for 3) as s,
+ substring('foo' similar 'f' escape '#') as ss,
+ substring('foo' from 'oo') as ssf, -- historically-permitted abuse
+ trim(' ' from ' foo ') as bt,
+ trim(leading ' ' from ' foo ') as lt,
+ trim(trailing ' foo ') as rt;
+select pg_get_viewdef('tt201v', true);
+ pg_get_viewdef
+-----------------------------------------------------------------------------------------------
+ SELECT date_part('day'::text, now()) AS extr, +
+ ((now(), '@ 1 day'::interval) OVERLAPS (CURRENT_TIMESTAMP(2), '@ 1 day'::interval)) AS o,+
+ (('foo'::text) IS NORMALIZED) AS isn, +
+ (('foo'::text) IS NFKC NORMALIZED) AS isnn, +
+ NORMALIZE('foo'::text) AS n, +
+ NORMALIZE('foo'::text, NFKD) AS nfkd, +
+ OVERLAY('foo'::text PLACING 'bar'::text FROM 2) AS ovl, +
+ OVERLAY('foo'::text PLACING 'bar'::text FROM 2 FOR 3) AS ovl2, +
+ POSITION(('foo'::text) IN ('foobar'::text)) AS p, +
+ SUBSTRING('foo'::text FROM 2 FOR 3) AS s, +
+ SUBSTRING('foo'::text SIMILAR 'f'::text ESCAPE '#'::text) AS ss, +
+ "substring"('foo'::text, 'oo'::text) AS ssf, +
+ TRIM(BOTH ' '::text FROM ' foo '::text) AS bt, +
+ TRIM(LEADING ' '::text FROM ' foo '::text) AS lt, +
+ TRIM(TRAILING FROM ' foo '::text) AS rt;
+(1 row)
+
-- corner cases with empty join conditions
create view tt21v as
select * from tt5 natural inner join tt6;
@@ -1904,7 +1943,7 @@ drop cascades to view aliased_view_2
drop cascades to view aliased_view_3
drop cascades to view aliased_view_4
DROP SCHEMA testviewschm2 CASCADE;
-NOTICE: drop cascades to 67 other objects
+NOTICE: drop cascades to 68 other objects
DETAIL: drop cascades to table t1
drop cascades to view temporal1
drop cascades to view temporal2
@@ -1966,6 +2005,7 @@ drop cascades to view tt17v
drop cascades to view tt18v
drop cascades to view tt19v
drop cascades to view tt20v
+drop cascades to view tt201v
drop cascades to view tt21v
drop cascades to view tt22v
drop cascades to view tt23v
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index 639b50308e..c300965554 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -2726,10 +2726,10 @@ create temp table tmptz (f1 timestamptz primary key);
insert into tmptz values ('2017-01-18 00:00+00');
explain (costs off)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on tmptz
- Filter: (timezone('utc'::text, f1) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
+ Filter: ((f1 AT TIME ZONE 'utc'::text) = 'Wed Jan 18 00:00:00 2017'::timestamp without time zone)
(2 rows)
select * from tmptz where f1 at time zone 'utc' = '2017-01-18 00:00';
diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql
index e7af0bf2fa..6d4dd53965 100644
--- a/src/test/regress/sql/create_view.sql
+++ b/src/test/regress/sql/create_view.sql
@@ -586,6 +586,28 @@ select * from
cast(1+2 as int8) as i8;
select pg_get_viewdef('tt20v', true);
+-- reverse-listing of various special function syntaxes required by SQL
+
+create view tt201v as
+select
+ extract(day from now()) as extr,
+ (now(), '1 day'::interval) overlaps
+ (current_timestamp(2), '1 day'::interval) as o,
+ 'foo' is normalized isn,
+ 'foo' is nfkc normalized isnn,
+ normalize('foo') as n,
+ normalize('foo', nfkd) as nfkd,
+ overlay('foo' placing 'bar' from 2) as ovl,
+ overlay('foo' placing 'bar' from 2 for 3) as ovl2,
+ position('foo' in 'foobar') as p,
+ substring('foo' from 2 for 3) as s,
+ substring('foo' similar 'f' escape '#') as ss,
+ substring('foo' from 'oo') as ssf, -- historically-permitted abuse
+ trim(' ' from ' foo ') as bt,
+ trim(leading ' ' from ' foo ') as lt,
+ trim(trailing ' foo ') as rt;
+select pg_get_viewdef('tt201v', true);
+
-- corner cases with empty join conditions
create view tt21v as
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
Here is a new patch for this. This now follows the implementation that Tom has suggested: Leave date_part() alone, add a new set of extract() functions, and map the SQL EXTRACT construct to those. I have basically just copied over the implementations from my previous patch and placed them next to the existing date_part() implementations. So all the behavior is still the same as in the previous patches. One thing I still need to look into is how to not lose all the test coverage for date_part(). But that should be fairly mechanical, so I'm leaving it off in this version. -- Peter Eisentraut 2ndQuadrant, an EDB company https://www.2ndquadrant.com/
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 15.03.21 18:35, Tom Lane wrote:
> Anyway, taking a quick look at the v4 patch, the only complaint
> I have is that it seems a bit bulky and brute-force to duplicate
> so much code. Is it feasible to share most of the implementation
> between old and new functions, returning (say) an int64 that can
> then be converted to either numeric or float8 by a wrapper? That
> would also reduce the pressure to duplicate all the test cases.
Yeah, it's not straightforward to do this, because you'd also need to
carry around scale and infinity information, so you might end up
creating a mini-numeric implementation just for this.
An easy way to reduce duplication would be to convert the existing
date_part() into a wrapper around the new extract(), with a cast. But
then you'd pay the performance penalty of the numeric version.
Which leads me to: After retesting this now, with a new machine, the
performance of the numeric implementation is brutal compared to the
float implementation, for cases where we need numeric division, which is
milliseconds, seconds, and epoch. In the first two cases, I imagine we
could rewrite this a bit to avoid a lot of the numeric work, but for the
epoch case (which is what started this thread), there isn't enough space
in int64 to make this work. Perhaps int128 could be pressed into
service, optionally. I think it would also help if we cracked open the
numeric APIs a bit to avoid all the repeated unpacking and packing for
each step.
So I think we need to do a bit more thinking and work here, meaning it
will have to be postponed.
Here are the kinds of tests I ran:
=> select date_part('epoch', localtime + generate_series(0, 10000000) *
interval '1 second') \g /dev/null
Time: 2537.482 ms (00:02.537)
=> select extract(epoch from localtime + generate_series(0, 10000000) *
interval '1 second') \g /dev/null
Time: 6106.586 ms (00:06.107)
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 18.03.21 09:28, Peter Eisentraut wrote: > Which leads me to: After retesting this now, with a new machine, the > performance of the numeric implementation is brutal compared to the > float implementation, for cases where we need numeric division, which is > milliseconds, seconds, and epoch. In the first two cases, I imagine we > could rewrite this a bit to avoid a lot of the numeric work, but for the > epoch case (which is what started this thread), there isn't enough space > in int64 to make this work. Perhaps int128 could be pressed into > service, optionally. I think it would also help if we cracked open the > numeric APIs a bit to avoid all the repeated unpacking and packing for > each step. > > So I think we need to do a bit more thinking and work here, meaning it > will have to be postponed. Well, I had an idea that I put to work. In most of these cases where we need division, we divide an integer by a power of 10. That can be done with numeric very quickly by just shifting the weight and scale around. So I wrote a function that does that specifically (look for int64_div_fast_to_numeric()). With that, the slow cases I mentioned now have the same performance as the other cases that didn't have any numeric division. You just get the overhead for constructing and passing around a numeric instead of a double, which can't be avoided. So here is an intermediate patch that does this. I haven't gotten rid of all numeric_div_opt_error() calls yet, but if this seems acceptable, I can work on the remaining ones.
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> Well, I had an idea that I put to work. In most of these cases where we
> need division, we divide an integer by a power of 10. That can be done
> with numeric very quickly by just shifting the weight and scale around.
> So I wrote a function that does that specifically (look for
> int64_div_fast_to_numeric()). With that, the slow cases I mentioned now
> have the same performance as the other cases that didn't have any
> numeric division. You just get the overhead for constructing and
> passing around a numeric instead of a double, which can't be avoided.
Yeah, I was wondering if we could do something like that, but I hadn't
got as far as figuring a way to deal with divisors not a multiple of
NBASE.
Looking at the proposed code, I wonder if it wouldn't be better to
define the function as taking the base-10-log of the divisor, so that
you'd have the number of digits to shift (and the dscale) immediately
instead of needing repeated integer divisions to get that. Also, the
risk of intermediate overflow here seems annoying:
+ if (unlikely(pg_mul_s64_overflow(val1, NBASE/x, &val1)))
+ elog(ERROR, "overflow");
Maybe that's unreachable for the ranges of inputs the current patch could
create, but it seems like it makes the function distinctly less
general-purpose than one would think from its comment. Maybe, if that
overflows, we could handle the failure by making that adjustment after
we've converted to numeric?
> So here is an intermediate patch that does this. I haven't gotten rid
> of all numeric_div_opt_error() calls yet, but if this seems acceptable,
> I can work on the remaining ones.
I guess the immediate question is how much of a performance gap there
is now between the float and numeric implementations.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 19.03.21 21:06, Tom Lane wrote:
> I guess the immediate question is how much of a performance gap there
> is now between the float and numeric implementations.
Attached are my test script and the full output.
To summarize, for cases that don't do any interesting computation and
where the overhead is only the data type passing, the difference is like
this:
-- old
select date_part('microseconds', current_timestamp + generate_series(0,
10000000) * interval '1 second') \g /dev/null
Time: 2760.966 ms (00:02.761)
-- new
select extract(microseconds from current_timestamp + generate_series(0,
10000000) * interval '1 second') \g /dev/null
Time: 3178.477 ms (00:03.178)
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 19.03.21 21:06, Tom Lane wrote:
>> I guess the immediate question is how much of a performance gap there
>> is now between the float and numeric implementations.
> Attached are my test script and the full output.
OK ... I prefer to do this sort of timing in a way that's not so
dependent on client I/O speeds, along the lines of
select count(date_part('day', current_date + g * interval '1 day')) from generate_series(0, :N) g;
I applied the v5 patch and ran your test suite that way, producing
the attached results. It looks pretty promising for me, too.
Most of the cases show about 10%-15% degradation:
# select extract, date_part, extract/date_part as ratio, unit from (select sum(msec) filter (where fn = 'extract') as
extract,sum(msec) filter (where fn = 'date_part') as date_part, unit from timings group by unit) ss order by ratio;
extract | date_part | ratio | unit
-----------+-----------+------------------------+-----------------
22690.100 | 20705.402 | 1.09585411575201486066 | decade
22810.005 | 20754.296 | 1.09904980636298142804 | century
11238.122 | 10190.385 | 1.10281623314526389337 | timezone_minute
20201.992 | 18303.982 | 1.1036938301184955 | doy
20121.073 | 18206.290 | 1.1051715094069138 | dow
23209.090 | 20915.715 | 1.10964841507928368693 | millennium
18839.455 | 16943.063 | 1.11192734159106886399 | week
20130.843 | 18010.011 | 1.1177585066438882 | isoyear
19755.296 | 17668.497 | 1.11810846163089027890 | isodow
22500.373 | 20112.264 | 1.11873894455641592612 | day
22631.485 | 20200.266 | 1.12035579135443067928 | month
22883.344 | 20407.733 | 1.12130749652594925659 | quarter
22628.524 | 20172.361 | 1.12175882634660365239 | year
26503.545 | 23493.288 | 1.12813263941598979249 | minute
26381.817 | 23329.924 | 1.13081452815705700542 | hour
27236.886 | 24070.860 | 1.13152940941869131390 | microseconds
11563.820 | 9948.148 | 1.1624093248311143 | timezone_hour
27728.212 | 23567.973 | 1.17652086583771968849 | second
28348.328 | 23984.219 | 1.18195751965073367617 | milliseconds
49902.129 | 30798.034 | 1.6203024193037776 | epoch
31544.035 | 18250.745 | 1.7283697186060076 | julian
(21 rows)
The outliers are epoch and julian, which unsurprisingly are the
ones you didn't fix yet.
I think a ten-percent-ish slowdown is acceptable for this purpose,
so I think if you can address the points already raised then we're
pretty much good to go with this.
regards, tom lane
\set ECHO all
\timing on
\set N 10000000
-- date
select count(date_part('day', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('month', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('quarter', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('week', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('year', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('decade', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('century', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('millennium', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('julian', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('isoyear', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('dow', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('isodow', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('doy', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(date_part('epoch', current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(day from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(month from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(quarter from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(week from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(year from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(decade from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(century from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(millennium from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(julian from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(isoyear from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(dow from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(isodow from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(doy from current_date + g * interval '1 day')) from generate_series(0, :N) g;
select count(extract(epoch from current_date + g * interval '1 day')) from generate_series(0, :N) g;
-- time
select count(date_part('microseconds', localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('milliseconds', localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('second', localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('minute', localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('hour', localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('epoch', localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(microseconds from localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(milliseconds from localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(second from localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(minute from localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(hour from localtime + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(epoch from localtime + g * interval '1 second')) from generate_series(0, :N) g;
-- timetz
select count(date_part('microseconds', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('milliseconds', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('second', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('minute', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('hour', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('timezone_minute', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('timezone_hour', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('epoch', current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(microseconds from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(milliseconds from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(second from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(minute from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(hour from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(timezone_minute from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(timezone_hour from current_time + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(epoch from current_time + g * interval '1 second')) from generate_series(0, :N) g;
-- timestamp
select count(date_part('microseconds', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('milliseconds', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('second', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('minute', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('hour', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('day', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('month', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('quarter', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('week', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('year', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('decade', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('century', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('millennium', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('julian', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('isoyear', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('dow', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('isodow', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('doy', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('epoch', localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(microseconds from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(milliseconds from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(second from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(minute from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(hour from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(day from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(month from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(quarter from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(week from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(year from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(decade from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(century from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(millennium from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(julian from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(isoyear from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(dow from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(isodow from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(doy from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(epoch from localtimestamp + g * interval '1 second')) from generate_series(0, :N) g;
-- timestamptz
select count(date_part('microseconds', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('milliseconds', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('second', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('minute', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('hour', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('day', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('month', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('quarter', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('week', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('year', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('decade', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('century', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('millennium', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('julian', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('isoyear', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('dow', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('isodow', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('doy', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('timezone_minute', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('timezone_hour', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('epoch', current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(microseconds from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(milliseconds from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(second from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(minute from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(hour from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(day from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(month from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(quarter from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(week from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(year from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(decade from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(century from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(millennium from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(julian from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(isoyear from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(dow from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(isodow from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(doy from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(timezone_minute from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(timezone_hour from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(epoch from current_timestamp + g * interval '1 second')) from generate_series(0, :N) g;
-- interval
select count(date_part('microseconds', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('milliseconds', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('second', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('minute', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('hour', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('day', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('month', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('quarter', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('year', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('decade', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('century', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('millennium', g * interval '1 second')) from generate_series(0, :N) g;
select count(date_part('epoch', g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(microseconds from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(milliseconds from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(second from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(minute from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(hour from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(day from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(month from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(quarter from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(year from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(decade from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(century from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(millennium from g * interval '1 second')) from generate_series(0, :N) g;
select count(extract(epoch from g * interval '1 second')) from generate_series(0, :N) g;
--
-- PostgreSQL database dump
--
-- Dumped from database version 14devel
-- Dumped by pg_dump version 14devel
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET default_toast_compression = 'pglz';
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: timings; Type: TABLE; Schema: public; Owner: postgres
--
CREATE TABLE public.timings (
msec numeric,
fn text,
unit text,
value text
);
ALTER TABLE public.timings OWNER TO postgres;
--
-- Data for Name: timings; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY public.timings (msec, fn, unit, value) FROM stdin;
5180.515 date_part day current_date + g * interval '1 day'
5203.787 date_part month current_date + g * interval '1 day'
5306.675 date_part quarter current_date + g * interval '1 day'
5414.957 date_part week current_date + g * interval '1 day'
5197.415 date_part year current_date + g * interval '1 day'
5233.880 date_part decade current_date + g * interval '1 day'
5259.760 date_part century current_date + g * interval '1 day'
5377.774 date_part millennium current_date + g * interval '1 day'
5917.525 date_part julian current_date + g * interval '1 day'
5759.533 date_part isoyear current_date + g * interval '1 day'
5754.813 date_part dow current_date + g * interval '1 day'
5655.134 date_part isodow current_date + g * interval '1 day'
6028.292 date_part doy current_date + g * interval '1 day'
5991.237 date_part epoch current_date + g * interval '1 day'
5771.943 extract day current_date + g * interval '1 day'
5776.346 extract month current_date + g * interval '1 day'
5838.696 extract quarter current_date + g * interval '1 day'
6017.475 extract week current_date + g * interval '1 day'
5797.537 extract year current_date + g * interval '1 day'
5810.704 extract decade current_date + g * interval '1 day'
5838.451 extract century current_date + g * interval '1 day'
5881.487 extract millennium current_date + g * interval '1 day'
9049.921 extract julian current_date + g * interval '1 day'
6353.854 extract isoyear current_date + g * interval '1 day'
6330.941 extract dow current_date + g * interval '1 day'
6202.410 extract isodow current_date + g * interval '1 day'
6348.026 extract doy current_date + g * interval '1 day'
10145.366 extract epoch current_date + g * interval '1 day'
4283.332 date_part microseconds localtime + g * interval '1 second'
4206.173 date_part milliseconds localtime + g * interval '1 second'
4111.950 date_part second localtime + g * interval '1 second'
4091.874 date_part minute localtime + g * interval '1 second'
4120.468 date_part hour localtime + g * interval '1 second'
4323.501 date_part epoch localtime + g * interval '1 second'
4786.541 extract microseconds localtime + g * interval '1 second'
4999.648 extract milliseconds localtime + g * interval '1 second'
4892.576 extract second localtime + g * interval '1 second'
4681.172 extract minute localtime + g * interval '1 second'
4605.876 extract hour localtime + g * interval '1 second'
5136.200 extract epoch localtime + g * interval '1 second'
4411.680 date_part microseconds current_time + g * interval '1 second'
4432.563 date_part milliseconds current_time + g * interval '1 second'
4374.950 date_part second current_time + g * interval '1 second'
4294.787 date_part minute current_time + g * interval '1 second'
4258.783 date_part hour current_time + g * interval '1 second'
4563.066 date_part timezone_minute current_time + g * interval '1 second'
4457.659 date_part timezone_hour current_time + g * interval '1 second'
4647.430 date_part epoch current_time + g * interval '1 second'
5075.710 extract microseconds current_time + g * interval '1 second'
5233.760 extract milliseconds current_time + g * interval '1 second'
5126.352 extract second current_time + g * interval '1 second'
4872.501 extract minute current_time + g * interval '1 second'
4889.788 extract hour current_time + g * interval '1 second'
4955.501 extract timezone_minute current_time + g * interval '1 second'
5015.217 extract timezone_hour current_time + g * interval '1 second'
5377.584 extract epoch current_time + g * interval '1 second'
5955.346 date_part microseconds localtimestamp + g * interval '1 second'
5935.035 date_part milliseconds localtimestamp + g * interval '1 second'
5848.145 date_part second localtimestamp + g * interval '1 second'
5831.416 date_part minute localtimestamp + g * interval '1 second'
5803.652 date_part hour localtimestamp + g * interval '1 second'
5769.468 date_part day localtimestamp + g * interval '1 second'
5819.778 date_part month localtimestamp + g * interval '1 second'
5849.743 date_part quarter localtimestamp + g * interval '1 second'
6014.519 date_part week localtimestamp + g * interval '1 second'
5784.959 date_part year localtimestamp + g * interval '1 second'
6172.807 date_part decade localtimestamp + g * interval '1 second'
6222.657 date_part century localtimestamp + g * interval '1 second'
6182.300 date_part millennium localtimestamp + g * interval '1 second'
6583.064 date_part julian localtimestamp + g * interval '1 second'
6367.773 date_part isoyear localtimestamp + g * interval '1 second'
6344.974 date_part dow localtimestamp + g * interval '1 second'
6246.871 date_part isodow localtimestamp + g * interval '1 second'
6382.862 date_part doy localtimestamp + g * interval '1 second'
6596.429 date_part epoch localtimestamp + g * interval '1 second'
6496.251 extract microseconds localtimestamp + g * interval '1 second'
6761.816 extract milliseconds localtimestamp + g * interval '1 second'
6631.913 extract second localtimestamp + g * interval '1 second'
6392.654 extract minute localtimestamp + g * interval '1 second'
6354.535 extract hour localtimestamp + g * interval '1 second'
6346.387 extract day localtimestamp + g * interval '1 second'
6360.977 extract month localtimestamp + g * interval '1 second'
6434.271 extract quarter localtimestamp + g * interval '1 second'
6542.497 extract week localtimestamp + g * interval '1 second'
6362.930 extract year localtimestamp + g * interval '1 second'
6387.618 extract decade localtimestamp + g * interval '1 second'
6434.422 extract century localtimestamp + g * interval '1 second'
6466.045 extract millennium localtimestamp + g * interval '1 second'
11359.495 extract julian localtimestamp + g * interval '1 second'
6919.297 extract isoyear localtimestamp + g * interval '1 second'
6910.778 extract dow localtimestamp + g * interval '1 second'
6787.230 extract isodow localtimestamp + g * interval '1 second'
6948.939 extract doy localtimestamp + g * interval '1 second'
10450.228 extract epoch localtimestamp + g * interval '1 second'
5453.363 date_part microseconds current_timestamp + g * interval '1 second'
5450.235 date_part milliseconds current_timestamp + g * interval '1 second'
5346.234 date_part second current_timestamp + g * interval '1 second'
5324.099 date_part minute current_timestamp + g * interval '1 second'
5302.169 date_part hour current_timestamp + g * interval '1 second'
5328.442 date_part day current_timestamp + g * interval '1 second'
5319.765 date_part month current_timestamp + g * interval '1 second'
5351.117 date_part quarter current_timestamp + g * interval '1 second'
5513.587 date_part week current_timestamp + g * interval '1 second'
5290.259 date_part year current_timestamp + g * interval '1 second'
5406.541 date_part decade current_timestamp + g * interval '1 second'
5370.052 date_part century current_timestamp + g * interval '1 second'
5417.243 date_part millennium current_timestamp + g * interval '1 second'
5750.156 date_part julian current_timestamp + g * interval '1 second'
5882.705 date_part isoyear current_timestamp + g * interval '1 second'
6106.503 date_part dow current_timestamp + g * interval '1 second'
5766.492 date_part isodow current_timestamp + g * interval '1 second'
5892.828 date_part doy current_timestamp + g * interval '1 second'
5627.319 date_part timezone_minute current_timestamp + g * interval '1 second'
5490.489 date_part timezone_hour current_timestamp + g * interval '1 second'
5187.254 date_part epoch current_timestamp + g * interval '1 second'
6291.630 extract microseconds current_timestamp + g * interval '1 second'
6442.536 extract milliseconds current_timestamp + g * interval '1 second'
6336.514 extract second current_timestamp + g * interval '1 second'
6102.246 extract minute current_timestamp + g * interval '1 second'
6104.173 extract hour current_timestamp + g * interval '1 second'
6060.887 extract day current_timestamp + g * interval '1 second'
6096.743 extract month current_timestamp + g * interval '1 second'
6130.391 extract quarter current_timestamp + g * interval '1 second'
6279.483 extract week current_timestamp + g * interval '1 second'
6132.714 extract year current_timestamp + g * interval '1 second'
6123.841 extract decade current_timestamp + g * interval '1 second'
6131.588 extract century current_timestamp + g * interval '1 second'
6173.388 extract millennium current_timestamp + g * interval '1 second'
11134.619 extract julian current_timestamp + g * interval '1 second'
6857.692 extract isoyear current_timestamp + g * interval '1 second'
6879.354 extract dow current_timestamp + g * interval '1 second'
6765.656 extract isodow current_timestamp + g * interval '1 second'
6905.027 extract doy current_timestamp + g * interval '1 second'
6282.621 extract timezone_minute current_timestamp + g * interval '1 second'
6548.603 extract timezone_hour current_timestamp + g * interval '1 second'
9169.715 extract epoch current_timestamp + g * interval '1 second'
3967.139 date_part microseconds g * interval '1 second'
3960.213 date_part milliseconds g * interval '1 second'
3886.694 date_part second g * interval '1 second'
3951.112 date_part minute g * interval '1 second'
3844.852 date_part hour g * interval '1 second'
3833.839 date_part day g * interval '1 second'
3856.936 date_part month g * interval '1 second'
3900.198 date_part quarter g * interval '1 second'
3899.728 date_part year g * interval '1 second'
3892.174 date_part decade g * interval '1 second'
3901.827 date_part century g * interval '1 second'
3938.398 date_part millennium g * interval '1 second'
4052.183 date_part epoch g * interval '1 second'
4586.754 extract microseconds g * interval '1 second'
4910.568 extract milliseconds g * interval '1 second'
4740.857 extract second g * interval '1 second'
4454.972 extract minute g * interval '1 second'
4427.445 extract hour g * interval '1 second'
4321.156 extract day g * interval '1 second'
4397.419 extract month g * interval '1 second'
4479.986 extract quarter g * interval '1 second'
4335.343 extract year g * interval '1 second'
4367.937 extract decade g * interval '1 second'
4405.544 extract century g * interval '1 second'
4688.170 extract millennium g * interval '1 second'
9623.036 extract epoch g * interval '1 second'
\.
--
-- PostgreSQL database dump complete
--
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 19.03.21 21:06, Tom Lane wrote: > Yeah, I was wondering if we could do something like that, but I hadn't > got as far as figuring a way to deal with divisors not a multiple of > NBASE. > > Looking at the proposed code, I wonder if it wouldn't be better to > define the function as taking the base-10-log of the divisor, so that > you'd have the number of digits to shift (and the dscale) immediately > instead of needing repeated integer divisions to get that. done that way, much simpler now > Also, the > risk of intermediate overflow here seems annoying: > > + if (unlikely(pg_mul_s64_overflow(val1, NBASE/x, &val1))) > + elog(ERROR, "overflow"); > > Maybe that's unreachable for the ranges of inputs the current patch could > create, but it seems like it makes the function distinctly less > general-purpose than one would think from its comment. Maybe, if that > overflows, we could handle the failure by making that adjustment after > we've converted to numeric? also done I also figured out a way to combine the float8 and numeric implementations so that there is not so much duplication. Added tests to cover all the edge and overflow cases. I think this is solid now. The extract(julian from timestamp) is still a bit in the slow mode, but as I previously stated, it's not documented and gives the wrong result, so it's not clear whether it should be fixed and what it should do. I think I'll register that part as an open item in any case, to see what we should do about that.
Attachment
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Peter Eisentraut
Date:
On 01.04.21 20:49, Peter Eisentraut wrote: > also done > > I also figured out a way to combine the float8 and numeric > implementations so that there is not so much duplication. Added tests > to cover all the edge and overflow cases. > > I think this is solid now. > > The extract(julian from timestamp) is still a bit in the slow mode, but > as I previously stated, it's not documented and gives the wrong result, > so it's not clear whether it should be fixed and what it should do. I > think I'll register that part as an open item in any case, to see what > we should do about that. committed and done
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> The extract(julian from timestamp) is still a bit in the slow mode, but
> as I previously stated, it's not documented and gives the wrong result,
> so it's not clear whether it should be fixed and what it should do. I
> think I'll register that part as an open item in any case, to see what
> we should do about that.
I looked into this issue. It's not quite true that the behavior is
entirely undocumented: Appendix B (datetime.sgml) says
In the Julian Date system, each day has a sequential number, starting
from JD 0 (which is sometimes called <emphasis>the</emphasis> Julian Date).
JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
24 November 4714 BC in the Gregorian calendar. Julian Date counting
is most often used by astronomers for labeling their nightly observations,
and therefore a date runs from noon UTC to the next noon UTC, rather than
from midnight to midnight: JD 0 designates the 24 hours from noon UTC on
24 November 4714 BC to noon UTC on 25 November 4714 BC.
</para>
<para>
Although <productname>PostgreSQL</productname> supports Julian Date notation for
input and output of dates (and also uses Julian dates for some internal
datetime calculations), it does not observe the nicety of having dates
run from noon to noon. <productname>PostgreSQL</productname> treats a Julian Date
as running from midnight to midnight.
</para>
That last bit requires clarification: we treat a Julian date as running
from *local* midnight to local midnight (ie in the active timezone, not
UTC midnight). So far as I can see, the behavior of extract(julian) is
consistent with that definition:
regression=# show timezone;
TimeZone
------------------
America/New_York
(1 row)
regression=# select date_part('julian', '2021-04-19 00:00:01-04'::timestamptz);
date_part
-------------------
2459324.000011574
(1 row)
regression=# select date_part('julian', '2021-04-19 23:59:00-04'::timestamptz);
date_part
--------------------
2459324.9993055556
(1 row)
regression=# select date_part('julian', '2021-04-19'::date);
date_part
-----------
2459324
(1 row)
I don't see that to_char's J mode differs from this, either.
So I don't think there's any code change required (unless you are still
worried about speed). What we do need is documentation fixes:
* clarify the above bit about local vs UTC midnight
* document the existence of the julian field for date_part/extract
* fix this bit in the to_char docs to agree with reality,
ie s/UTC/local time/:
<row>
<entry><literal>J</literal></entry>
<entry>Julian Day (integer days since November 24, 4714 BC at midnight UTC)</entry>
</row>
Perhaps it'd be worth documenting that you can get the standard
astronomical definition of Julian date by transposing to time zone UTC-12
before converting. But I think trying to change PG's behavior at this
point would be a bad idea.
(We could also consider back-patching these doc fixes.)
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
I wrote:
> So I don't think there's any code change required (unless you are still
> worried about speed). What we do need is documentation fixes:
> * clarify the above bit about local vs UTC midnight
> * document the existence of the julian field for date_part/extract
> * fix this bit in the to_char docs to agree with reality,
> ie s/UTC/local time/:
> <entry>Julian Day (integer days since November 24, 4714 BC at midnight UTC)</entry>
> Perhaps it'd be worth documenting that you can get the standard
> astronomical definition of Julian date by transposing to time zone UTC-12
> before converting. But I think trying to change PG's behavior at this
> point would be a bad idea.
Here's a concrete documentation proposal covering this.
regards, tom lane
diff --git a/doc/src/sgml/datetime.sgml b/doc/src/sgml/datetime.sgml
index 39fbc39cb0..c069281d1a 100644
--- a/doc/src/sgml/datetime.sgml
+++ b/doc/src/sgml/datetime.sgml
@@ -763,9 +763,6 @@
<indexterm zone="datetime-units-history">
<primary>Gregorian calendar</primary>
</indexterm>
- <indexterm zone="datetime-units-history">
- <primary>Julian date</primary>
- </indexterm>
<para>
The SQL standard states that <quote>Within the definition of a
@@ -868,6 +865,15 @@ $ <userinput>cal 9 1752</userinput>
festivals.
</para>
+ </sect1>
+
+ <sect1 id="datetime-julian-dates">
+ <title>Julian Dates</title>
+
+ <indexterm zone="datetime-julian-dates">
+ <primary>Julian date</primary>
+ </indexterm>
+
<para>
The <firstterm>Julian Date</firstterm> system is another type of
calendar, unrelated to the Julian calendar though it is confusingly
@@ -876,6 +882,9 @@ $ <userinput>cal 9 1752</userinput>
Joseph Justus Scaliger (1540–1609)
and probably takes its name from Scaliger's father,
the Italian scholar Julius Caesar Scaliger (1484–1558).
+ </para>
+
+ <para>
In the Julian Date system, each day has a sequential number, starting
from JD 0 (which is sometimes called <emphasis>the</emphasis> Julian Date).
JD 0 corresponds to 1 January 4713 BC in the Julian calendar, or
@@ -891,7 +900,26 @@ $ <userinput>cal 9 1752</userinput>
input and output of dates (and also uses Julian dates for some internal
datetime calculations), it does not observe the nicety of having dates
run from noon to noon. <productname>PostgreSQL</productname> treats a Julian Date
- as running from midnight to midnight.
+ as running from local midnight to local midnight, the same as a normal
+ date.
+ </para>
+
+ <para>
+ This definition does, however, provide a way to obtain the astronomical
+ definition when you need it: do the arithmetic in time
+ zone <literal>UTC-12</literal>. For example,
+<programlisting>
+=> SELECT extract(julian from '2021-06-23 7:00:00-04'::timestamptz at time zone 'UTC-12');
+ extract
+------------------------------
+ 2459389.95833333333333333333
+(1 row)
+=> SELECT extract(julian from '2021-06-23 8:00:00-04'::timestamptz at time zone 'UTC-12');
+ extract
+--------------------------------------
+ 2459390.0000000000000000000000000000
+(1 row)
+</programlisting>
</para>
</sect1>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5bba13973f..105555cfb4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -7539,7 +7539,8 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
</row>
<row>
<entry><literal>J</literal></entry>
- <entry>Julian Day (integer days since November 24, 4714 BC at midnight UTC)</entry>
+ <entry>Julian Date (integer days since November 24, 4714 BC at local
+ midnight; see <xref linkend="datetime-julian-dates"/>)</entry>
</row>
<row>
<entry><literal>Q</literal></entry>
@@ -9609,6 +9610,25 @@ SELECT EXTRACT(ISOYEAR FROM DATE '2006-01-02');
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>julian</literal></term>
+ <listitem>
+ <para>
+ The <firstterm>Julian Date</firstterm> corresponding to the
+ date or timestamp (not applicable to intervals). Timestamps
+ that are not local midnight result in a fractional value. See
+ <xref linkend="datetime-julian-dates"/> for more information.
+ </para>
+
+<screen>
+SELECT EXTRACT(JULIAN FROM DATE '2006-01-01');
+<lineannotation>Result: </lineannotation><computeroutput>2453737</computeroutput>
+SELECT EXTRACT(JULIAN FROM TIMESTAMP '2006-01-01 12:00');
+<lineannotation>Result: </lineannotation><computeroutput>2453737.50000000000000000000</computeroutput>
+</screen>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>microseconds</literal></term>
<listitem>
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
I wrote:
>> Perhaps it'd be worth documenting that you can get the standard
>> astronomical definition of Julian date by transposing to time zone UTC-12
>> before converting.
BTW ... I'd first thought that the way to do this was to rotate to
time zone UTC+12. I convinced myself on two separate days that UTC-12
was correct instead, but now I'm thinking I was right the first time.
In particular, the results I'm getting with UTC-12 don't square with
the example on Wikipedia [1], which says "the Julian Date for
00:30:00.0 UT January 1, 2013, is 2 456 293.520 833":
regression=# select extract(julian from '2013-01-01 00:30+00'::timestamptz at time zone 'utc-12');
extract
------------------------------
2456294.52083333333333333333
(1 row)
But using UTC+12 does match:
regression=# select extract(julian from '2013-01-01 00:30+00'::timestamptz at time zone 'utc+12');
extract
------------------------------
2456293.52083333333333333333
(1 row)
Of course Wikipedia has been known to contain errors, but now
I'm inclined to think I blew this. Anyone want to check my work?
regards, tom lane
[1] https://en.wikipedia.org/wiki/Julian_day
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Thomas Munro
Date:
On Wed, Apr 28, 2021 at 3:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Of course Wikipedia has been known to contain errors, but now
> I'm inclined to think I blew this. Anyone want to check my work?
I tried a couple of examples not from Wikipedia. First, from the
definition of Julian days as used by astronomers[1], counting from
noon on 4713-01-01 BC Julian AKA 4714-11-24 BC Gregorian, days 0 and 1
look right with 'utc+12':
postgres=# select extract(julian from '4714-11-24 11:00:00+00
BC'::timestamptz at time zone 'utc+12');
ERROR: timestamp out of range
postgres=# select extract(julian from '4714-11-24 12:00:00+00
BC'::timestamptz at time zone 'utc+12');
extract
--------------------------------
0.0000000000000000000000000000
(1 row)
postgres=# select extract(julian from '4714-11-25 11:00:00+00
BC'::timestamptz at time zone 'utc+12');
extract
------------------------
0.95833333333333333333
(1 row)
postgres=# select extract(julian from '4714-11-25 12:00:00+00
BC'::timestamptz at time zone 'utc+12');
extract
--------------------------------
1.0000000000000000000000000000
(1 row)
Next I found a worked example in an aerospace textbook[1] and it agrees, too:
postgres=# select extract(julian from '2004-05-12
14:45:30+00'::timestamptz at time zone 'utc+12');
extract
------------------------------
2453138.11493055555555555556
(1 row)
[1]
http://curious.astro.cornell.edu/people-and-astronomy/125-observational-astronomy/timekeeping/calendars/763-how-was-the-starting-point-for-the-julian-date-system-chosen-advanced
[2] https://www.sciencedirect.com/topics/engineering/julian-day-number
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Thomas Munro <thomas.munro@gmail.com> writes:
> On Wed, Apr 28, 2021 at 3:56 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Of course Wikipedia has been known to contain errors, but now
>> I'm inclined to think I blew this. Anyone want to check my work?
> I tried a couple of examples not from Wikipedia. ...
Thanks for checking! I'll go adjust the documentation.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Noah Misch
Date:
On Tue, Nov 03, 2020 at 07:22:14PM -0500, Tom Lane wrote: > I feel like this is committable at this point --- any objections? (This became commit 40c24bf, "Improve our ability to regurgitate SQL-syntax function calls.") > --- a/src/backend/nodes/equalfuncs.c > +++ b/src/backend/nodes/equalfuncs.c > @@ -2369,11 +2369,12 @@ _equalFuncCall(const FuncCall *a, const FuncCall *b) > + COMPARE_SCALAR_FIELD(funcformat); equalfuncs.c has been using COMPARE_COERCIONFORM_FIELD() to ignore differences in fields of this type. Does this spot have cause to depart from the pattern?
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
Noah Misch <noah@leadboat.com> writes:
> equalfuncs.c has been using COMPARE_COERCIONFORM_FIELD() to ignore differences
> in fields of this type. Does this spot have cause to depart from the pattern?
Oversight, I think. Will fix.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
I wrote:
> Noah Misch <noah@leadboat.com> writes:
>> equalfuncs.c has been using COMPARE_COERCIONFORM_FIELD() to ignore differences
>> in fields of this type. Does this spot have cause to depart from the pattern?
> Oversight, I think. Will fix.
After looking closer, I see that there are a couple of very very minor
ways in which parse analysis changes behavior based on the value of
FuncCall.funcformat:
* transformRangeFunction won't apply the appropriate transformation to
a multiple-argument unnest() unless the format is COERCE_EXPLICIT_CALL.
(This is likely a no-op, though, as no grammar production that emits
COERCE_SQL_SYNTAX could apply to the function name "unnest".)
* ParseFuncOrColumn will not believe that a FuncCall could_be_projection
unless the format is COERCE_EXPLICIT_CALL. This is next door to a no-op,
since other restrictions such as nargs == 1 would usually suffice to
reject COERCE_SQL_SYNTAX calls, but maybe there are corner cases where
it'd matter.
So if you wanted to be picky you could claim that within FuncCall,
funcformat is semantically significant and thus that equalfuncs.c is
coded correctly. Nonetheless I'm inclined to think that it'd be better
to use COMPARE_COERCIONFORM_FIELD here. I'm quite sure I didn't make
the above analysis when I wrote the code; using COMPARE_SCALAR_FIELD
was just reflex.
We could make use of COMPARE_COERCIONFORM_FIELD 100% correct by removing
these two tests of the funcformat value, but on the whole I doubt that
would be better.
BTW, I'm not sure any of this matters anyway; do we ever use equal()
on raw parse trees, except for debug purposes?
Thoughts?
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Tom Lane
Date:
I wrote:
> We could make use of COMPARE_COERCIONFORM_FIELD 100% correct by removing
> these two tests of the funcformat value, but on the whole I doubt that
> would be better.
On still closer inspection, that seems like it'd be fine. All of
the gram.y productions that emit COERCE_SQL_SYNTAX also produce
schema-qualified function names (via SystemFuncName); and it seems
hard to see a use-case where we'd not do that. This makes the two
checks I cited 100% redundant, because the conditions they are in
also insist on an unqualified function name. So let's just take them
out again, making it strictly OK to use COMPARE_COERCIONFORM_FIELD.
regards, tom lane
Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
From
Noah Misch
Date:
On Sun, Jun 06, 2021 at 03:10:07PM -0400, Tom Lane wrote: > I wrote: > > We could make use of COMPARE_COERCIONFORM_FIELD 100% correct by removing > > these two tests of the funcformat value, but on the whole I doubt that > > would be better. > > On still closer inspection, that seems like it'd be fine. All of > the gram.y productions that emit COERCE_SQL_SYNTAX also produce > schema-qualified function names (via SystemFuncName); and it seems > hard to see a use-case where we'd not do that. This makes the two > checks I cited 100% redundant, because the conditions they are in > also insist on an unqualified function name. So let's just take them > out again, making it strictly OK to use COMPARE_COERCIONFORM_FIELD. I have little intuition on this exact topic, but I have no particular concerns about the change you pushed.