Hi Jian, On Wed, Jun 10, 2026 jian he <[email protected]> wrote: > SELECT first_value(1); > ERROR: window function first_value requires an OVER clause > LINE 1: SELECT first_value(1); > ^ > > select prosrc, prokind, proname from pg_proc > where proname = 'prev' or proname = 'first' or proname = 'last' or > proname = 'next'; > > I am wondering, why the above query result functions not marked as > window function in catalog?
The short answer is that no catalog marking can fix this, because prokind does not take part in name resolution. func_get_detail() picks the candidate by name/arguments/search_path first, and prokind (window or not) is only inspected afterwards. So marking the placeholders as window functions would leave all three collisions in place: next(10) still ambiguous against public.next(numeric), an exact-signature public.prev(integer) still silently winning inside DEFINE, and the deparsed bare PREV( still rebinding under an ordinary search_path. The deeper reason is that whether these names are navigation operations depends on a context the catalog cannot see -- are we inside a DEFINE clause or not? Any approach that goes through the catalog, under any prokind, resolves the name before it can know whether it is even a navigation operation. So the only thing that actually works is to not rely on the catalog at all: inside a DEFINE clause, recognize these names before any catalog lookup and substitute the navigation operation directly. Concretely I plan to drop the eight catalog entries (OIDs 8126-8133) and, inside DEFINE, intercept an unqualified prev/next/first/last by name in ParseFuncOrColumn -- ahead of func_get_detail() -- binding it to the navigation operation with no fallback to function resolution. Outside DEFINE the names then resolve as ordinary identifiers (a same-name user function just works), and a schema-qualified call inside DEFINE (public.prev(...)) still reaches the user function as the explicit escape hatch. This is also what Oracle does -- there is no catalog object for the navigation operations there at all. Thanks for the pointer -- the first_value comparison is what made it clear that the robustness comes from context, not from prokind. Henson
