Message-ID: <da61a3315f0f011da1ad5dead781b80a871a783e.camel@cybertec.at>
Subject: Re: pg_upgrade failure due to dependencies
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: "Edward J. Sabol" <edwardjsabol@gmail.com>, Pgsql-admin
	 <pgsql-admin@lists.postgresql.org>
Date: Tue, 01 Jul 2025 19:09:21 +0200
In-Reply-To: <D305115C-0461-4F41-89EB-3BFCC11C240A@gmail.com>
References: <396f2fb636248f329c152c6eca189f4348fec726.camel@cybertec.at>
	 <D305115C-0461-4F41-89EB-3BFCC11C240A@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-1.fc42) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/da61a3315f0f011da1ad5dead781b80a871a783e.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-07-01 at 07:10 -0400, Edward J. Sabol wrote:
> On Jul 1, 2025, at 5:37=E2=80=AFAM, Laurenz Albe <laurenz.albe@cybertec.a=
t> wrote:
> > Moreover, PostgreSQL cannot check dependencies, since the function was =
defined using
> > the "old"=C2=A0style for SQL functions, where the function body is just=
 a string.
> > If they had used the standard=C2=A0conforming new style, PostgreSQL wou=
ld try to trach
> > dependencies. =C2=A0Not sure if that would have been=C2=A0enough to avo=
id the problem,
> > but clearly better.
>=20
> Just for my edification, how would one create that PostGIS function using=
 the
> "standard-conforming new style"?

CREATE FUNCTION public.st_transform(geom geometry, from_proj text, to_srid =
integer)
 RETURNS geometry
 IMMUTABLE PARALLEL SAFE STRICT COST 5000
BEGIN ATOMIC
   SELECT public.postgis_transform_geometry($1, $2, proj4text, $3)
   FROM public.spatial_ref_sys WHERE srid=3D$3;
END;

(The IMMUTABLE would still be a lie, though.)

> *All* of the examples in PostgeSQL's current documentation are the "old" =
(string)
> style at=C2=A0https://www.postgresql.org/docs/18/xfunc-sql.html.
>=20
> The only example of the new style in the PostgreSQL documentation that I =
could
> find is the very trivial second example at
> https://www.postgresql.org/docs/18/sql-createfunction.html. The other exa=
mples
> on that web page are all the old style.
>=20
> If the new style is better, perhaps someone should update the PostgreSQL
> documentation to use it more than just once. More complicated examples of
> "new style" SQL functions are needed, I feel.

That is true; we could do more there.

Yours,
Laurenz Albe