MIME-Version: 1.0
References: 
 <CAAAe_zAZDuHSiVGvz9c6h=Pe=aN+FKZOrdNPfbTOk3XV+WFKYQ@mail.gmail.com>
 <CAAAe_zDz3z2Paidk3jHOm9S3eMVLoXRxK0Lyo=5i_9-EfSH7fA@mail.gmail.com>
 <20260604.132108.405136284364833955.ishii@postgresql.org>
 <20260609.171307.1883356507067957349.ishii@postgresql.org>
 <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>
In-Reply-To: 
 <CACJufxHJFNBJ0vHCCLestWV5b7DF5e4VzfmovqGKBGgqg+rcGA@mail.gmail.com>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Thu, 11 Jun 2026 14:05:42 +0900
Message-ID: 
 <CAAAe_zBY0rrgf+tKXMUc-Y3nDDD69hddRBKopEKAZobhY=Cy-Q@mail.gmail.com>
Subject: Re: Row pattern recognition
To: jian he <jian.universality@gmail.com>, Tatsuo Ishii <ishii@postgresql.org>
Cc: zsolt.parragi@percona.com, sjjang112233@gmail.com,
 vik@postgresfriends.org,
	er@xs4all.nl, jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
	peter@eisentraut.org, li.evan.chao@gmail.com, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000009afd3d0653f350a5"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zBY0rrgf%2BtKXMUc-Y3nDDD69hddRBKopEKAZobhY%3DCy-Q%40mail.gmail.com>
Precedence: bulk

--0000000000009afd3d0653f350a5
Content-Type: text/plain; charset="UTF-8"

Hi Jian,

On Wed, Jun 10, 2026 jian he <jian.universality@gmail.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

--0000000000009afd3d0653f350a5
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Jian,<br><br>On Wed, Jun 10, 2026 jian he &lt;<a href=
=3D"mailto:jian.universality@gmail.com">jian.universality@gmail.com</a>&gt;=
 wrote:<br>&gt; SELECT first_value(1);<br>&gt; ERROR: =C2=A0window function=
 first_value requires an OVER clause<br>&gt; LINE 1: SELECT first_value(1);=
<br>&gt; =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0^<br>&gt;<b=
r>&gt; select prosrc, prokind, proname from pg_proc<br>&gt; where proname =
=3D &#39;prev&#39; or proname =3D &#39;first&#39; or proname =C2=A0=3D &#39=
;last&#39; or<br>&gt; proname =3D &#39;next&#39;;<br>&gt;<br>&gt; I am wond=
ering, why the above query result functions not marked as<br>&gt; window fu=
nction in catalog?<br><br>The short answer is that no catalog marking can f=
ix this, because prokind<br>does not take part in name resolution. =C2=A0fu=
nc_get_detail() picks the<br>candidate by name/arguments/search_path first,=
 and prokind (window or not)<br>is only inspected afterwards.=C2=A0 So mark=
ing the placeholders as window<br>functions would leave all three collision=
s in place: next(10) still<br>ambiguous against public.next(numeric), an ex=
act-signature<br>public.prev(integer) still silently winning inside DEFINE,=
 and the<br>deparsed bare PREV( still rebinding under an ordinary search_pa=
th.<br><br>The deeper reason is that whether these names are navigation ope=
rations<br>depends on a context the catalog cannot see -- are we inside a D=
EFINE<br>clause or not?=C2=A0 Any approach that goes through the catalog, u=
nder any<br>prokind, resolves the name before it can know whether it is eve=
n a<br>navigation operation.<br><br>So the only thing that actually works i=
s to not rely on the catalog at all:<br>inside a DEFINE clause, recognize t=
hese names before any catalog lookup and<br>substitute the navigation opera=
tion directly.=C2=A0 Concretely I plan to drop<br>the eight catalog entries=
 (OIDs 8126-8133) and, inside DEFINE, intercept an<br>unqualified prev/next=
/first/last by name in ParseFuncOrColumn -- ahead of<br>func_get_detail() -=
- binding it to the navigation operation with no<br>fallback to function re=
solution.=C2=A0 Outside DEFINE the names then resolve as<br>ordinary identi=
fiers (a same-name user function just works), and a<br>schema-qualified cal=
l inside DEFINE (public.prev(...)) still reaches the<br>user function as th=
e explicit escape hatch.=C2=A0 This is also what Oracle does<br>-- there is=
 no catalog object for the navigation operations there at all.<br><br>Thank=
s for the pointer -- the first_value comparison is what made it clear<br>th=
at the robustness comes from context, not from prokind.<br><br>Henson<br></=
div>

--0000000000009afd3d0653f350a5--