MIME-Version: 1.0
References: 
 <CAK3STzGm-8_5yvyWm_SJALo1Wb1i3cMJrBj8=WPmVyxHdhmYrg@mail.gmail.com>
 <3126567.1696689510@sss.pgh.pa.us>
In-Reply-To: <3126567.1696689510@sss.pgh.pa.us>
From: jinser <aimer@purejs.icu>
Date: Sun, 8 Oct 2023 00:18:55 +0800
Message-ID: 
 <CAK3STzEs8ABpm6bhhHjNWmuskTFed2_+Fohk+JeBaA+o=X5s8g@mail.gmail.com>
Subject: Re: Is `DATE` a function?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: jinser <aimer@purejs.icu>, pgsql-novice@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAK3STzEs8ABpm6bhhHjNWmuskTFed2_%2BFohk%2BJeBaA%2Bo%3DX5s8g%40mail.gmail.com>
Precedence: bulk

Thank you so much for your explanation.
With the reminder of =E2=80=9Dcast function=E2=80=9C, I found a more detail=
ed
explanation in the document that I missed before:
https://www.postgresql.org/docs/16/sql-expressions.html#SQL-SYNTAX-TYPE
-CASTS.

Thanks again everyone :)

Tom Lane <tgl@sss.pgh.pa.us> =E4=BA=8E2023=E5=B9=B410=E6=9C=887=E6=97=A5=E5=
=91=A8=E5=85=AD 22:38=E5=86=99=E9=81=93=EF=BC=9A
>
> jinser <aimer@purejs.icu> writes:
> > playground=3D# SELECT DATE('2022-01-13');
> >     date
> > ------------
> >  2022-01-13
> > (1 row)
>
> Sure, there are functions named date():
>
> postgres=3D# \df date
>                              List of functions
>    Schema   | Name | Result data type |     Argument data types     | Typ=
e
> ------------+------+------------------+-----------------------------+----=
--
>  pg_catalog | date | date             | timestamp with time zone    | fun=
c
>  pg_catalog | date | date             | timestamp without time zone | fun=
c
> (2 rows)
>
> The reason these aren't explicitly documented is that they are intended a=
s
> implementation support for casts.
>
> postgres=3D# \dC date
>                                       List of casts
>          Source type         |         Target type         |  Function   =
|   Implicit?
> -----------------------------+-----------------------------+-------------=
+---------------
>  date                        | timestamp with time zone    | timestamptz =
| yes
>  date                        | timestamp without time zone | timestamp   =
| yes
>  timestamp with time zone    | date                        | date        =
| in assignment
>  timestamp without time zone | date                        | date        =
| in assignment
> (4 rows)
>
> Hence, the preferred spelling is more like
>
>         select now()::date;
>
> or if you want to be SQL-spec-compatible,
>
>         select cast(now() as date);
>
> but for historical reasons we like to let you also write
>
>         select date(now());
>
> which is managed (in most cases) by naming cast implementation
> functions the same as the target type.
>
> > Another reason I think this is a function is that other types don't
> > seem to have the same behavior:
>
> > playground=3D# SELECT integer('123');
> > ERROR:  syntax error at or near "("
>
> You're running into a couple of things there: INTEGER is a reserved
> word, and the cast functions for that type are named after the
> internal type name "int4".
>
> postgres=3D# \dC integer
>                               List of casts
>    Source type    |   Target type    |      Function      |   Implicit?
> ------------------+------------------+--------------------+--------------=
-
>  "char"           | integer          | int4               | no
>  bigint           | integer          | int4               | in assignment
>  bit              | integer          | int4               | no
>  boolean          | integer          | int4               | no
>  double precision | integer          | int4               | in assignment
>  integer          | "char"           | char               | no
>  ...
>
> postgres=3D# select int4('123');
>  int4
> ------
>   123
> (1 row)
>
> Note that none of these have anything to do with the syntax for
> a typed literal, which is "type-name quoted-literal" with no
> parentheses:
>
> postgres=3D# select date 'today';
>     date
> ------------
>  2023-10-07
> (1 row)
>
> postgres=3D# select integer '42';
>  int4
> ------
>    42
> (1 row)
>
> Some aspects of the behavior might look the same, but there
> are a lot of edge cases.
>
>                         regards, tom lane
>