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>
In-Reply-To: 
 <CAAAe_zBL+J0AYmvmcJQT7Q-gp5aRH0deJ7SE7-N21g4hWExyJw@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Wed, 10 Jun 2026 18:48:01 +0800
Message-ID: 
 <CACJufxHJFNBJ0vHCCLestWV5b7DF5e4VzfmovqGKBGgqg+rcGA@mail.gmail.com>
Subject: Re: Row pattern recognition
To: assam258@gmail.com
Cc: Tatsuo Ishii <ishii@postgresql.org>, 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: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CACJufxHJFNBJ0vHCCLestWV5b7DF5e4VzfmovqGKBGgqg%2BrcGA%40mail.gmail.com>
Precedence: bulk

On Wed, Jun 10, 2026 at 2:10=E2=80=AFPM Henson Choi <assam258@gmail.com> wr=
ote:
>
> Hi Tatsuo, Jian,
>
>
> =3D=3D 2. PREV/NEXT/FIRST/LAST placeholders collide with user functions =
=3D=3D
>
> The nav operations are polymorphic pg_catalog functions (anyelement, OIDs
> 8126-8133) recognized by funcid in parse_func.c, which collides with
> same-name user functions.
>
> Outside DEFINE, a same-name function masks or clashes with the placeholde=
r:
> with public.last(anyelement), SELECT last(123) fails "cannot use last
> outside a DEFINE clause"; with public.next(numeric), SELECT next(10) fail=
s
> "function next(integer) is not unique"; and even with no user function,
> last(123) errors instead of "function last(integer) does not exist".
>
> Inside DEFINE, a same-name function with an exact-type match beats the
> anyelement placeholder, so PREV(price) silently becomes a plain FuncExpr
> instead of an RPRNavExpr -- a wrong match result with no error (reproduce=
d
> for numeric, text and int).  And ruleutils deparses a bare PREV(, so
> reparsing a view under a search_path with public.prev rebinds it (pg_dump
> is safe via search_path =3D '').
>
> This is original v47 design, not a regression.  Per the standard,
> PREV/NEXT/FIRST/LAST are navigation operations with dedicated syntax, not
> general-namespace functions -- the collision comes from mapping them onto
> catalog functions plus search-path resolution.
>
> I haven't found a clean approach yet.  Inside DEFINE these names have to =
be
> the navigation operation (per the standard), yet outside DEFINE they
> shouldn't shadow or break same-name user functions the way the catalog
> placeholders do -- and since the deparse output is unqualified (a bare
> PREV(...)), whatever we choose also has to round-trip cleanly.  I'm not
> sure how best to reconcile those.
>
> My rough leaning is to not add catalog functions for these at all: leave
> resolution outside DEFINE exactly as it is today, and only inside DEFINE
> adjust the function-resolution path itself to recognize the navigation
> operations.  But that is still quite abstract.
>
> Question: how would you approach this?
>

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 =3D 'prev' or proname =3D 'first' or proname  =3D 'last' or
proname =3D 'next';

I am wondering, why the above query result functions not makred as
window function in catalog?