On 10/21/21 12:52, Bryn Llewellyn 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".
https://www.postgresql.org/docs/current/catalog-pg-type.html
select oid from pg_type where typname = 'date';
oid
------
1082
https://www.postgresql.org/docs/current/catalog-pg-cast.html
select * from pg_cast where castsource = 1082;
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?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
