PostgreSQL - CAST vs :: operator on LATERAL table function

It is a bit weird, yes, but the grammar will only accept something syntactically similar to a function call in a function-in-FROM expression.

So that is indeed a trick you can use if you want an arbitrary expression in a FROM clause: surround it with an unnecessary CAST expression.

PostgreSQL will happily treat anything that looks like a function as a table function in such a context.

Excellent corner case examples. Both of these syntax variants are "explicit type casts", doing exactly the same. It just so happens that some special locations in SQL code only allow functional notation to avoid ambiguities.

As for your second observation:

One other location at which an explicit CAST is required:

CREATE INDEX ON ... ( CAST(<straw> AS <gold>) );

Shorthand syntax can actually be used here - with an additional set of parentheses to make it unambiguous:

CREATE INDEX ON ... ((<straw>::<gold>));

db<>fiddle here

And either syntax variant matches the other expression in queries, too. See:

• Can we create index for key/value of JSONB data type?
• How to create an index on an integer json property in postgres
• Spliting Datetime into 2 Columns for Restaurant Reservation Schema?

(There are more efficient ways to do what your first example does, but that's probably beside the point.)