Le mercredi 1 septembre 2021, 19:27:35 heure normale d’Europe centrale Tom
Lane a écrit :
> The rest of this is stuck pending investigation of the ideas about
> making new-style function creation safer when the creation-time path
> isn't secure, so I suppose we should mark it RWF rather than leaving
> it in the queue. Will go do that.
Sorry to revive such an old thread but ... since the introduction of the
@extschema:name@ syntax in 72a5b1fc880481914da2d4233077438dd87840ca we can now
proceed with this or am I missing something ?
I've updated the previous patches to convert them to the new-style, added one
for lo as well. Most of them are just patching against a newer extension
version than what was provided at the time, except for earthdistance which now
references everything explicitly by pg_catalog or @extschema:cube@.
This is a bit tangential to the initial subject, but take the opportunity to
also update the CREATE DOMAIN statement in earthdistance extension to
explicitly reference the cube schema.
Best regards,
--
Ronan Dunklau
>From 35eee2d4dbfc741cb940029a94bde30242b8b6e0 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunk...@aiven.io>
Date: Mon, 28 Oct 2024 16:25:52 +0100
Subject: [PATCH v2 5/6] Use "new style" SQL function in xml2 extension
Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
contrib/xml2/Makefile | 4 +++-
contrib/xml2/xml2.control | 2 +-
2 files changed, 4 insertions(+), 2 deletions(-)
diff --git a/contrib/xml2/Makefile b/contrib/xml2/Makefile
index 0d703fe0e8f..8597e9aa9c5 100644
--- a/contrib/xml2/Makefile
+++ b/contrib/xml2/Makefile
@@ -7,7 +7,9 @@ OBJS = \
xslt_proc.o
EXTENSION = xml2
-DATA = xml2--1.1.sql xml2--1.0--1.1.sql
+DATA = xml2--1.1.sql \
+ xml2--1.1--1.2.sql \
+ xml2--1.0--1.1.sql
PGFILEDESC = "xml2 - XPath querying and XSLT"
REGRESS = xml2
diff --git a/contrib/xml2/xml2.control b/contrib/xml2/xml2.control
index ba2c0599a37..b32156c949e 100644
--- a/contrib/xml2/xml2.control
+++ b/contrib/xml2/xml2.control
@@ -1,6 +1,6 @@
# xml2 extension
comment = 'XPath querying and XSLT'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/pgxml'
# XXX do we still need this to be non-relocatable?
relocatable = false
--
2.47.0
>From 0f513936c95b52c8801d8987b3b9cb2c9060d627 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunk...@aiven.io>
Date: Tue, 29 Oct 2024 07:53:02 +0100
Subject: [PATCH v2 6/6] Use "new style" SQL function in lo extension
---
contrib/lo/Makefile | 2 +-
contrib/lo/lo--1.1--1.2.sql | 9 +++++++++
contrib/lo/lo.control | 2 +-
3 files changed, 11 insertions(+), 2 deletions(-)
create mode 100644 contrib/lo/lo--1.1--1.2.sql
diff --git a/contrib/lo/Makefile b/contrib/lo/Makefile
index 716893859eb..8cfff63d882 100644
--- a/contrib/lo/Makefile
+++ b/contrib/lo/Makefile
@@ -3,7 +3,7 @@
MODULES = lo
EXTENSION = lo
-DATA = lo--1.1.sql lo--1.0--1.1.sql
+DATA = lo--1.1.sql lo--1.0--1.1.sql lo--1.1--1.2.sql
PGFILEDESC = "lo - management for large objects"
REGRESS = lo
diff --git a/contrib/lo/lo--1.1--1.2.sql b/contrib/lo/lo--1.1--1.2.sql
new file mode 100644
index 00000000000..4cad3e689dd
--- /dev/null
+++ b/contrib/lo/lo--1.1--1.2.sql
@@ -0,0 +1,9 @@
+/* contrib/lo/lo--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION lo UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION lo_oid(lo) RETURNS pg_catalog.oid
+LANGUAGE SQL STRICT IMMUTABLE PARALLEL SAFE
+RETURN (SELECT $1::pg_catalog.oid);
+
diff --git a/contrib/lo/lo.control b/contrib/lo/lo.control
index f73f8b5fae5..3acb3665a4e 100644
--- a/contrib/lo/lo.control
+++ b/contrib/lo/lo.control
@@ -1,6 +1,6 @@
# lo extension
comment = 'Large Object maintenance'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/lo'
relocatable = true
trusted = true
--
2.47.0
>From 1ba5dfd577a57e845f2e375e56d22c24208107c9 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunk...@aiven.io>
Date: Mon, 28 Oct 2024 16:24:46 +0100
Subject: [PATCH v2 4/6] Use "new style" SQL function in pg_freespacemap
extension
Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
contrib/pg_freespacemap/Makefile | 4 +++-
.../pg_freespacemap/pg_freespacemap--1.2--1.3.sql | 13 +++++++++++++
contrib/pg_freespacemap/pg_freespacemap.control | 2 +-
3 files changed, 17 insertions(+), 2 deletions(-)
create mode 100644 contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
diff --git a/contrib/pg_freespacemap/Makefile b/contrib/pg_freespacemap/Makefile
index b48e4b255bc..a986ae73d69 100644
--- a/contrib/pg_freespacemap/Makefile
+++ b/contrib/pg_freespacemap/Makefile
@@ -6,7 +6,9 @@ OBJS = \
pg_freespacemap.o
EXTENSION = pg_freespacemap
-DATA = pg_freespacemap--1.1.sql pg_freespacemap--1.1--1.2.sql \
+DATA = pg_freespacemap--1.1.sql \
+ pg_freespacemap--1.2--1.3.sql \
+ pg_freespacemap--1.1--1.2.sql \
pg_freespacemap--1.0--1.1.sql
PGFILEDESC = "pg_freespacemap - monitoring of free space map"
diff --git a/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
new file mode 100644
index 00000000000..7f92c9e92e3
--- /dev/null
+++ b/contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql
@@ -0,0 +1,13 @@
+/* contrib/pg_freespacemap/pg_freespacemap--1.2--1.3.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pg_freespacemap UPDATE TO '1.3'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION
+ pg_freespace(rel regclass, blkno OUT bigint, avail OUT int2)
+RETURNS SETOF RECORD
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+ SELECT blkno, pg_freespace($1, blkno) AS avail
+ FROM generate_series(0, pg_relation_size($1) / current_setting('block_size')::bigint - 1) AS blkno;
+END;
diff --git a/contrib/pg_freespacemap/pg_freespacemap.control b/contrib/pg_freespacemap/pg_freespacemap.control
index ac8fc5050a9..1992320691b 100644
--- a/contrib/pg_freespacemap/pg_freespacemap.control
+++ b/contrib/pg_freespacemap/pg_freespacemap.control
@@ -1,5 +1,5 @@
# pg_freespacemap extension
comment = 'examine the free space map (FSM)'
-default_version = '1.2'
+default_version = '1.3'
module_pathname = '$libdir/pg_freespacemap'
relocatable = true
--
2.47.0
>From 87335abb842f3864c18122c78f417f2d0f5024fc Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunk...@aiven.io>
Date: Mon, 28 Oct 2024 16:23:49 +0100
Subject: [PATCH v2 3/6] Use "new style" SQL function in pageinspect extension
Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
contrib/pageinspect/Makefile | 3 +-
.../pageinspect/pageinspect--1.12--1.13.sql | 74 +++++++++++++++++++
contrib/pageinspect/pageinspect.control | 2 +-
3 files changed, 77 insertions(+), 2 deletions(-)
create mode 100644 contrib/pageinspect/pageinspect--1.12--1.13.sql
diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 95e030b3969..9dee7653310 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -13,7 +13,8 @@ OBJS = \
rawpage.o
EXTENSION = pageinspect
-DATA = pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
+DATA = pageinspect--1.12--1.13.sql \
+ pageinspect--1.11--1.12.sql pageinspect--1.10--1.11.sql \
pageinspect--1.9--1.10.sql pageinspect--1.8--1.9.sql \
pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
pageinspect--1.5.sql pageinspect--1.5--1.6.sql \
diff --git a/contrib/pageinspect/pageinspect--1.12--1.13.sql b/contrib/pageinspect/pageinspect--1.12--1.13.sql
new file mode 100644
index 00000000000..f444fba58ce
--- /dev/null
+++ b/contrib/pageinspect/pageinspect--1.12--1.13.sql
@@ -0,0 +1,74 @@
+/* contrib/pageinspect/pageinspect--1.12--1.13.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION pageinspect UPDATE TO '1.13'" to load this file. \quit
+
+-- Convert SQL functions to new style
+
+CREATE OR REPLACE FUNCTION heap_page_item_attrs(
+ IN page bytea,
+ IN rel_oid regclass,
+ IN do_detoast bool,
+ OUT lp smallint,
+ OUT lp_off smallint,
+ OUT lp_flags smallint,
+ OUT lp_len smallint,
+ OUT t_xmin xid,
+ OUT t_xmax xid,
+ OUT t_field3 int4,
+ OUT t_ctid tid,
+ OUT t_infomask2 integer,
+ OUT t_infomask integer,
+ OUT t_hoff smallint,
+ OUT t_bits text,
+ OUT t_oid oid,
+ OUT t_attrs bytea[]
+ )
+RETURNS SETOF record
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+SELECT lp,
+ lp_off,
+ lp_flags,
+ lp_len,
+ t_xmin,
+ t_xmax,
+ t_field3,
+ t_ctid,
+ t_infomask2,
+ t_infomask,
+ t_hoff,
+ t_bits,
+ t_oid,
+ tuple_data_split(
+ rel_oid,
+ t_data,
+ t_infomask,
+ t_infomask2,
+ t_bits,
+ do_detoast)
+ AS t_attrs
+ FROM heap_page_items(page);
+END;
+
+CREATE OR REPLACE FUNCTION heap_page_item_attrs(IN page bytea, IN rel_oid regclass,
+ OUT lp smallint,
+ OUT lp_off smallint,
+ OUT lp_flags smallint,
+ OUT lp_len smallint,
+ OUT t_xmin xid,
+ OUT t_xmax xid,
+ OUT t_field3 int4,
+ OUT t_ctid tid,
+ OUT t_infomask2 integer,
+ OUT t_infomask integer,
+ OUT t_hoff smallint,
+ OUT t_bits text,
+ OUT t_oid oid,
+ OUT t_attrs bytea[]
+ )
+RETURNS SETOF record
+LANGUAGE SQL PARALLEL SAFE
+BEGIN ATOMIC
+SELECT * FROM heap_page_item_attrs(page, rel_oid, false);
+END;
diff --git a/contrib/pageinspect/pageinspect.control b/contrib/pageinspect/pageinspect.control
index b2804e9b128..cfc87feac03 100644
--- a/contrib/pageinspect/pageinspect.control
+++ b/contrib/pageinspect/pageinspect.control
@@ -1,5 +1,5 @@
# pageinspect extension
comment = 'inspect the contents of database pages at a low level'
-default_version = '1.12'
+default_version = '1.13'
module_pathname = '$libdir/pageinspect'
relocatable = true
--
2.47.0
>From 51ad1b5f7003fe898b6e15ff5b8db4c71bb81ba6 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunk...@aiven.io>
Date: Mon, 28 Oct 2024 15:34:45 +0100
Subject: [PATCH v2 2/6] Use "new style" SQL function in earthdistance
extension.
Since the sql_body is parsed at function definition time, and not when
executing it, the namespace is resolved at that point. This solves a
couple of bugs related to pg_restore failing to locate functions
belonging to an extension once they are inline.
The missing piece from the previous attempt was a syntax to reference
another extension's schema, which has been introduced in PG16. It is
then safe to fully-qualify every object reference either implicitly
because it has just been created, or by the extension schema it belongs
to.
Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
contrib/earthdistance/Makefile | 3 +-
.../earthdistance/earthdistance--1.1--1.2.sql | 58 +++++++++++
contrib/earthdistance/earthdistance--1.2.sql | 98 +++++++++++++++++++
contrib/earthdistance/earthdistance.control | 2 +-
4 files changed, 159 insertions(+), 2 deletions(-)
create mode 100644 contrib/earthdistance/earthdistance--1.1--1.2.sql
create mode 100644 contrib/earthdistance/earthdistance--1.2.sql
diff --git a/contrib/earthdistance/Makefile b/contrib/earthdistance/Makefile
index f93b7a925a2..0cf3fa379a2 100644
--- a/contrib/earthdistance/Makefile
+++ b/contrib/earthdistance/Makefile
@@ -3,7 +3,8 @@
MODULES = earthdistance
EXTENSION = earthdistance
-DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
+DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql \
+ earthdistance--1.1--1.2.sql
PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
REGRESS = earthdistance
diff --git a/contrib/earthdistance/earthdistance--1.1--1.2.sql b/contrib/earthdistance/earthdistance--1.1--1.2.sql
new file mode 100644
index 00000000000..f4508fed8f8
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.1--1.2.sql
@@ -0,0 +1,58 @@
+/* contrib/earthdistance/earthdistance--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION earthdistance UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+CREATE OR REPLACE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE OR REPLACE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE OR REPLACE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE OR REPLACE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE OR REPLACE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE OR REPLACE FUNCTION earth_box(earth, float8)
+RETURNS @extschema:cube@.cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
diff --git a/contrib/earthdistance/earthdistance--1.2.sql b/contrib/earthdistance/earthdistance--1.2.sql
new file mode 100644
index 00000000000..fac15969ad4
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.2.sql
@@ -0,0 +1,98 @@
+/* contrib/earthdistance/earthdistance--1.1.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION earthdistance" to load this file. \quit
+
+-- earth() returns the radius of the earth in meters. This is the only
+-- place you need to change things for the cube base distance functions
+-- in order to use different units (or a better value for the Earth's radius).
+
+CREATE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+-- Astronomers may want to change the earth function so that distances will be
+-- returned in degrees. To do this comment out the above definition and
+-- uncomment the one below. Note that doing this will break the regression
+-- tests.
+--
+-- CREATE FUNCTION earth() RETURNS float8
+-- LANGUAGE SQL IMMUTABLE
+-- AS 'SELECT 180/pi()';
+
+-- Define domain for locations on the surface of the earth using a cube
+-- datatype with constraints. cube provides 3D indexing.
+-- The cube is restricted to be a point, no more than 3 dimensions
+-- (for less than 3 dimensions 0 is assumed for the missing coordinates)
+-- and that the point must be very near the surface of the sphere
+-- centered about the origin with the radius of the earth.
+
+CREATE DOMAIN earth AS $extschema:cube@.cube
+ CONSTRAINT not_point check($extschema:cube@.cube_is_point(value))
+ CONSTRAINT not_3d check($extschema:cube@.cube_dim(value) <= 3)
+ CONSTRAINT on_surface check(abs($extschema:cube@.cube_distance(value, '(0)'::$extschema:cube@.cube) /
+ earth() - '1'::float8) < '10e-7'::float8);
+
+CREATE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pg_catalog.pi()*earth() ELSE 2*earth()*pg_catalog.asin($1/(2*earth())) END;
+
+CREATE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pg_catalog.pi() THEN 2*earth() ELSE 2*earth()*pg_catalog.sin($1/(2*earth())) END;
+
+CREATE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(earth()*pg_catalog.cos(radians($1))*pg_catalog.cos(radians($2))),earth()*pg_catalog.cos(radians($1))*pg_catalog.sin(radians($2))),earth()*pg_catalog.sin(radians($1)))::earth;
+
+CREATE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN @extschema:cube@.cube_ll_coord($1, 3)/earth() > 1 THEN 90::float8 ELSE pg_catalog.degrees(pg_catalog.asin(@extschema:cube@.cube_ll_coord($1, 3)/earth())) END;
+
+CREATE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN pg_catalog.degrees(pg_catalog.atan2(@extschema:cube@.cube_ll_coord($1, 2), @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE FUNCTION earth_box(earth, float8)
+RETURNS cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
+
+--------------- geo_distance
+
+CREATE FUNCTION geo_distance (point, point)
+RETURNS float8
+LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE AS 'MODULE_PATHNAME';
+
+--------------- geo_distance as operator <@>
+
+CREATE OPERATOR <@> (
+ LEFTARG = point,
+ RIGHTARG = point,
+ PROCEDURE = geo_distance,
+ COMMUTATOR = <@>
+);
diff --git a/contrib/earthdistance/earthdistance.control b/contrib/earthdistance/earthdistance.control
index 5816d22cdd9..de2465d487e 100644
--- a/contrib/earthdistance/earthdistance.control
+++ b/contrib/earthdistance/earthdistance.control
@@ -1,6 +1,6 @@
# earthdistance extension
comment = 'calculate great-circle distances on the surface of the Earth'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/earthdistance'
relocatable = true
requires = 'cube'
--
2.47.0
>From ababdd1ffc8ba6034ba693274bea1997a52ec061 Mon Sep 17 00:00:00 2001
From: Ronan Dunklau <ronan.dunk...@aiven.io>
Date: Mon, 28 Oct 2024 16:13:35 +0100
Subject: [PATCH v2 1/6] Use "new style" SQL function in citext extension
Author: Tom Lane
Discussion: https://www.postgresql.org/message-id/3395418.1618352794%40sss.pgh.pa.us
---
contrib/citext/Makefile | 1 +
contrib/citext/citext--1.7--1.8.sql | 60 +++++++++++++++++++++++++++++
contrib/citext/citext.control | 2 +-
3 files changed, 62 insertions(+), 1 deletion(-)
create mode 100644 contrib/citext/citext--1.7--1.8.sql
diff --git a/contrib/citext/Makefile b/contrib/citext/Makefile
index b9b3713f537..fc990607bf2 100644
--- a/contrib/citext/Makefile
+++ b/contrib/citext/Makefile
@@ -4,6 +4,7 @@ MODULES = citext
EXTENSION = citext
DATA = citext--1.4.sql \
+ citext--1.7--1.8.sql \
citext--1.6--1.7.sql \
citext--1.5--1.6.sql \
citext--1.4--1.5.sql \
diff --git a/contrib/citext/citext--1.7--1.8.sql b/contrib/citext/citext--1.7--1.8.sql
new file mode 100644
index 00000000000..7c95a9883aa
--- /dev/null
+++ b/contrib/citext/citext--1.7--1.8.sql
@@ -0,0 +1,60 @@
+/* contrib/citext/citext--1.7--1.8.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION citext UPDATE TO '1.8'" to load this file. \quit
+
+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' );
+
+CREATE OR REPLACE FUNCTION regexp_match(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_match( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 1
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_matches(string citext, pattern citext, flags text) RETURNS SETOF TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE ROWS 10
+RETURN pg_catalog.regexp_matches( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, 'i');
+
+CREATE OR REPLACE FUNCTION regexp_replace(string citext, pattern citext, replacement text, flags text) returns TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, $2::pg_catalog.text, $3, CASE WHEN pg_catalog.strpos($4, 'c') = 0 THEN $4 || 'i' ELSE $4 END);
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_array(string citext, pattern citext, flags text) RETURNS TEXT[]
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_array( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, 'i' );
+
+CREATE OR REPLACE FUNCTION regexp_split_to_table(string citext, pattern citext, flags text) RETURNS SETOF TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_split_to_table( $1::pg_catalog.text, $2::pg_catalog.text, CASE WHEN pg_catalog.strpos($3, 'c') = 0 THEN $3 || 'i' ELSE $3 END );
+
+CREATE OR REPLACE FUNCTION strpos( citext, citext ) RETURNS INT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.strpos( pg_catalog.lower( $1::pg_catalog.text ), pg_catalog.lower( $2::pg_catalog.text ) );
+
+CREATE OR REPLACE FUNCTION replace( citext, citext, citext ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.regexp_replace( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), $3::pg_catalog.text, 'gi' );
+
+CREATE OR REPLACE FUNCTION split_part( citext, citext, int ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN (pg_catalog.regexp_split_to_array( $1::pg_catalog.text, pg_catalog.regexp_replace($2::pg_catalog.text, '([^a-zA-Z_0-9])', E'\\\\\\1', 'g'), 'i'))[$3];
+
+CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS TEXT
+LANGUAGE SQL IMMUTABLE STRICT PARALLEL SAFE
+RETURN pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text, pg_catalog.lower($2::pg_catalog.text), $3), pg_catalog.upper($2::pg_catalog.text), $3);
diff --git a/contrib/citext/citext.control b/contrib/citext/citext.control
index f82265b3347..2b0f3fa8407 100644
--- a/contrib/citext/citext.control
+++ b/contrib/citext/citext.control
@@ -1,6 +1,6 @@
# citext extension
comment = 'data type for case-insensitive character strings'
-default_version = '1.7'
+default_version = '1.8'
module_pathname = '$libdir/citext'
relocatable = true
trusted = true
--
2.47.0
