Adrian Klaver wrote:
Bryn wrote:
I fear that I'm about to embarrass myself again. So I'll just ask for forgiveness in advance. Here's a simple test to get started. (All tests are done in a session where I set the timezone to 'UTC'.)
drop function if exists f(text) cascade;
drop function if exists f(timestamp) cascade;
drop function if exists f(timestamptz) cascade;
create function f(t in text)
returns text
language plpgsql
as $body$
begin
return 'plain "text" overload: '||t;
end;
$body$;
select f('2021-03-15'::date);
This causes the 42883 error, "function f(date) does not exist". I might've expected the system to have done an implicit conversion to "text" because this conversion is supported, thus:
select f(('2021-03-15'::date)::text);
This succeeds with this result:
plain "text" overload: 2021-03-15
There's clearly a rule at work here. For some reason, the implicit conversion from "date" to "text" is not considered to be acceptable.
From 10.3
"
Look for the best match.
Discard candidate functions for which the input types do not match and cannot be converted (using an implicit conversion) to match. unknown literals are assumed to be convertible to anything for this purpose. If only one candidate remains, use it; else continue to the next step.
"
See cast query below.
For some reason, the implicit conversion from "date" to "timestamptz" _is_ considered to be preferable to the implicit conversion from "date" to plain "timestamp".
52.62. pg_type — https://www.postgresql.org/docs/current/catalog-pg-type.html
select oid from pg_type where typname = 'date';
oid | castsource | casttarget | castfunc | castcontext | castmethod
-------+------------+------------+----------+-------------+------------
11421 | 1082 | 1114 | 2024 | i | f
11422 | 1082 | 1184 | 1174 | i | f
Note castcontext of 'i'(implicit) and only to timestamp types per below.
select typname, typispreferred from pg_type where oid in (1114, 1184);
typname | typispreferred
-------------+----------------
timestamp | f
timestamptz | t
typispreferred has timestmaptz as preferred cast.
I started with "38.6. Function Overloading", followed the link to "Chapter 10. Type Conversion" and started with "10.3. Functions". I read "If no exact match is found, see if the function call appears to be a special type conversion request…" as far as "Note that the “best match” rules are identical for operator and function type resolution." So I went to "10.2. Operators" and searched in the page for "timestamp". No hits. Where, in the PG doc, can I find a statement of the rules that allow me to predict the outcome of my tests?
Thank you very much, Adrian. This is just what I needed.
"10.3. Functions" doesn't mention "pg_type". And "52.62. pg_type" doesn't mention "pg_cast". So it's no wonder that I couldn't find what you showed me here. (At least, that's my excuse.)
Thanks, too, to David Johnston for your reply. Yes, I see now that the "10.1. Overview" page that starts the "Type Conversion" chapter does have lots of inflexions of the verb "prefer". And close to one of these there's a link to "Table 52.63" on the "52.62. pg_type" page. But I failed to spot that.
You said "implicit casting to text is bad". Yes, all implicit casting is, at best, potentially confusing for human code readers. I aim religiously to avoid this and always aim to use an explicit typecast instead.
And this brings me to what started me on this path today. "\df to_char" shows that while it has overloads for both plain "timestamp" and "timestamptz" date-time inputs, it has no "date" overload. Here's a contrived test:
deallocate all;
prepare s as
with c as (
select
'2021-06-15'::date as d,
'dd-Mon-yyyy TZH:TZM' as fmt)
select
rpad(current_setting('timezone'), 20) as "timezone",
to_char(d, fmt) as "implicit cast to timestamptz",
to_char(d::timestamptz, fmt) as "explicit cast to timestamptz",
to_char(d::timestamp, fmt) as "explicit cast to plain timestamp"
from c;
\t on
set timezone = 'Europe/Helsinki';
execute s;
set timezone = 'America/Los_Angeles';
execute s;
\t off
It gives the result that I'd expect:
Europe/Helsinki | 15-Jun-2021 +03:00 | 15-Jun-2021 +03:00 | 15-Jun-2021 +00:00
America/Los_Angeles | 15-Jun-2021 -07:00 | 15-Jun-2021 -07:00 | 15-Jun-2021 +00:00
And, given that nobody would include "TZH:TZM" in the template for rendering a date (except in this contrived test), then all three text renderings in this test would be identical.
However, it seems to me that the proper practice must be not to rely on intellectual analysis and the implicit cast. Rather, you must say that "date" is more like plain "timestamp" than it's like "timestamptz" (in that it knows nothing about timezones), and to write the explicit cast to plain "timestamp". But this leads to nastily cluttered code.
Why is there no "date" overload of "to_char()"?
| 