PostgreSQL - CAST vs :: operator on LATERAL table function

Question

While I can

SELECT
  elem[1], elem[2]
FROM
  (
    VALUES ('1,2'::TEXT)
  ) AS q(arr),
  LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;

using an explicit call to CAST, I can't

SELECT
  elem[1], elem[2]
FROM
  (
    VALUES ('1,2'::TEXT)
  ) AS q(arr),
  LATERAL String_To_Array(q.arr, ',')::INT[] AS elem
;

using the implicitly calling :: operator:

ERROR: syntax error at or near "::"

One other location at which an explicit CAST is required:

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

I doubt there is a syntactical reason, e.g. using extra enclosing parenthesis - which is incorrect here.

Is the explicit function call simply needed at this point as part of the low level implementation? Or does it follow any language rules?

Answer 1

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.)

Answer 2

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.