Re: pg_upgrade failure due to dependencies

On Tue, 2025-07-01 at 07:10 -0400, Edward J. Sabol wrote:
> On Jul 1, 2025, at 5:37 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Moreover, PostgreSQL cannot check dependencies, since the function was defined using
> > the "old" style for SQL functions, where the function body is just a string.
> > If they had used the standard conforming new style, PostgreSQL would try to trach
> > dependencies.  Not sure if that would have been enough to avoid the problem,
> > but clearly better.
>
> 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=$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 https://www.postgresql.org/docs/18/xfunc-sql.html.
>
> 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 examples
> on that web page are all the old style.
>
> 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