public inbox for [email protected]  

help / color / mirror / Atom feed

From: Henson Choi <[email protected]>
To: jian he <[email protected]>
To: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Thu, 11 Jun 2026 14:05:42 +0900
Message-ID: <CAAAe_zBY0rrgf+tKXMUc-Y3nDDD69hddRBKopEKAZobhY=Cy-Q@mail.gmail.com> (raw)
In-Reply-To: <CACJufxHJFNBJ0vHCCLestWV5b7DF5e4VzfmovqGKBGgqg+rcGA@mail.gmail.com>
References: <CAAAe_zAZDuHSiVGvz9c6h=Pe=aN+FKZOrdNPfbTOk3XV+WFKYQ@mail.gmail.com>
	<CAAAe_zDz3z2Paidk3jHOm9S3eMVLoXRxK0Lyo=5i_9-EfSH7fA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CACJufxFnwdQSApt2vWwYCd0gtf+JjFDxT2hbxHi=+dhFJc+-1g@mail.gmail.com>
	<CAAAe_zATnkqsbLYDj8MJV1TriX9Wi0wShDg3nK3qYpiupKwhFA@mail.gmail.com>
	<CAAAe_zBL+J0AYmvmcJQT7Q-gp5aRH0deJ7SE7-N21g4hWExyJw@mail.gmail.com>
	<CACJufxHJFNBJ0vHCCLestWV5b7DF5e4VzfmovqGKBGgqg+rcGA@mail.gmail.com>

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

view thread (118+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Row pattern recognition
  In-Reply-To: <CAAAe_zBY0rrgf+tKXMUc-Y3nDDD69hddRBKopEKAZobhY=Cy-Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox