Horton

Re: Converting contrib SQL functions to new style

Peter Eisentraut <peter@eisentraut.org>

View thread View on postgr.es
From: Peter Eisentraut <peter@eisentraut.org>
To: Ronan Dunklau <ronan.dunklau@aiven.io>, Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@lists.postgresql.org
Date: 2024-11-13T08:39:06Z
Lists: pgsql-hackers 
On 13.11.24 09:15, Ronan Dunklau wrote:
> Le mardi 12 novembre 2024, 09:30:30 heure normale d’Europe centrale Michael
> Paquier a écrit :
>> On Thu, Nov 07, 2024 at 10:06:37AM +0900, Michael Paquier wrote:
>>> Good point.  Checking all these contrib updates one-by-one is an ant's
>>> work, but I'll see if I can get at least some of them done on HEAD.
>>
>> I've begun looking at that a bit, and there are a couple of things
>> that we could do better with xml2 in 0005 at least in the context of
>> this patch: xpath_nodeset() and xpath_list() don't have any test
>> coverage.  That's not an issue directly related to this patch, but
>> perhaps we should add something for the functions that we are
>> manipulating after this upgrade path at least?  That's one way to
>> automatically make sure that these changes work the same way as the
>> original.
>>
>> The same argument comes up with lo_oid() in 0006.
> 
> Ok, please find attached a new complete patch series including tests for the
> uncovered functions. Tests pass both before and after the move to SQL-body
> functions.

By the way, if we're going to touch all these extension script files to 
make them more modern SQL-like, we could also use named parameters more. 
  For example,

+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) 
RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, 
$2::pg_catalog.text, 'i' );

could be

+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext) 
RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( string::pg_catalog.text, 
pattern::pg_catalog.text, 'i' );

etc.

Commits

  1. Use @extschema:name@ notation in contrib transform modules.

  2. pg_freespacemap: Fix declaration of pg_freespace(regclass)

  3. contrib/pageinspect: Use SQL-standard function bodies.

  4. contrib/xml2: Use SQL-standard function bodies.

  5. contrib/citext: Use SQL-standard function bodies.

  6. contrib/earthdistance: Use SQL-standard function bodies.

  7. contrib/lo: Use SQL-standard function bodies

  8. xml2: Add tests for functions xpath_nodeset() and xpath_list()

  9. contrib/lo: Add test for function lo_oid()

  10. pg_freespacemap: Use SQL-standard function bodies

  11. Add @extschema:name@ and no_relocate options to extensions.

  12. Make contrib modules' installation scripts more secure.