Re: Looking for a doc section that presents the overload selection rules - Mailing list pgsql-general

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@aklaver.com