From: Tom Lane <tgl@sss.pgh.pa.us>
To: Yushu Chen <gentcys@gmail.com>
cc: katja.henke@foo.ag, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19034: Recursive function with sql_body can replace an
 existing function but can not be created on it's own
In-reply-to: 
 <CAP5x3pZvk6D9b9mmKBEYKqZvJcDpH5OfZ419aVgzr+xXpo4O=w@mail.gmail.com>
References: <19034-de0857b4f94ec10c@postgresql.org>
 <CAP5x3pZvk6D9b9mmKBEYKqZvJcDpH5OfZ419aVgzr+xXpo4O=w@mail.gmail.com>
Comments: In-reply-to Yushu Chen <gentcys@gmail.com>
	message dated "Thu, 04 Sep 2025 17:03:34 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1030105.1756999498.1@sss.pgh.pa.us>
Date: Thu, 04 Sep 2025 11:24:58 -0400
Message-ID: <1030106.1756999498@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1030106.1756999498%40sss.pgh.pa.us>
Precedence: bulk

Yushu Chen <gentcys@gmail.com> writes:
> I ran a test on my local postgresql server, resulting in the same error as
> described.
> However, I wonder about the necessity of supporting recursive functions in
> the CREATE FUNCTION command alone?

This is a general property of SQL-standard functions: all objects
referenced in the function body have to exist before creating the
function.  Poking holes in that property would largely defeat the
point of having this type of function.

You could imagine supporting the case by making CREATE FUNCTION act
as though it were
  CREATE FUNCTION foo ... AS $$something dummy$$;
  CREATE OR REPLACE FUNCTION foo ... BEGIN real-body END;
However, that would be a significant amount of work to implement
and it'd about double the cost of doing CREATE FUNCTION.  Note
that this cost would probably have to be incurred for every
SQL-standard function, as I see no good way to detect whether
the body contains a recursive reference in advance of parsing it.

Given that recursive SQL functions are a tiny-minority use case
and there's already a perfectly serviceable way to make them
(ie use an old-style body), I seriously doubt that we'll do
anything about this request.

			regards, tom lane