Re: Converting contrib SQL functions to new style

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.