Re: Row pattern recognition

Hi Jian,

On Wed, Jun 10, 2026 jian he <jian(dot)universality(at)gmail(dot)com> 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