Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r1ytq-00EYYw-Bq for pgsql-general@arkaria.postgresql.org; Sun, 12 Nov 2023 01:04:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r1yto-0090Zf-Tp for pgsql-general@arkaria.postgresql.org; Sun, 12 Nov 2023 01:04:28 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r1yto-0090ZW-JD for pgsql-general@lists.postgresql.org; Sun, 12 Nov 2023 01:04:28 +0000 Received: from rs6162.luxsci.com ([162.142.76.255]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r1yth-0058Uj-Mm for pgsql-general@postgresql.org; Sun, 12 Nov 2023 01:04:27 +0000 Received: from pfortin.com (h241.116.117.75.dynamic.ip.windstream.net [75.117.116.241]) (authenticated bits=0) by rs6162.luxsci.com (8.15.2/8.14.9) with ESMTPSA id 3AC14HxF046064 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT); Sun, 12 Nov 2023 01:04:17 GMT Date: Sat, 11 Nov 2023 20:04:17 -0500 From: pf@pfortin.com To: Adrian Klaver Cc: pgsql-general Subject: Re: Aren't regex_*() functions built-in? Message-ID: <20231111200417.539bebb0@pfortin.com> In-Reply-To: References: <20231111191214.60d182b2.pfortin@pfortin.com> <03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com> <20231111192548.55392ea9@pfortin.com> X-Mailer: Claws Mail 4.1.1git67 (GTK 3.24.38; x86_64-pc-linux-gnu) MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Lux-Comment: Message 3AC14HxF046064 sent by user #22429 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pfortin.com; h=date:from:to:cc:subject:message-id:in-reply-to:references:mime-version:content-type:content-transfer-encoding; s=dkim; bh=ru8XMhYxJCM01tRP9QW3R9v67ws44tSQUfdXnIJQ7no=; b=d+5+KeJI6jRhPfJa7ANFRYUvzvpWE4ZcjHSIq2+ICQFuczo6npGH6p+/esKCc6N4SeXW/K0zTNobrEbsPCQMqqm5nTbGh02n3sAGT7aCVWHmpv+NOieCgpDh+lztRFGiR7TE3LWCCqlW6AN6Xt2w+ulDtlGg1LllW9m3bpXCzNo= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, 11 Nov 2023 16:53:01 -0800 Adrian Klaver wrote: >On 11/11/23 16:25, pf@pfortin.com wrote: >Reply to list also >Ccing list >> On Sat, 11 Nov 2023 16:16:20 -0800 Adrian Klaver wrote: >> > >>> Probably because it is spelled regexp_replace (). >> >> OK, found it in pg_catalog; but "create extension regexp_replace;" won't >> load it. How do I get regexp_* into public schema? > >Not sure why you are trying create extension regexp_replace;. > >The functions are already loaded: > >\df regexp_replace > List of functions > Schema | Name | Result data type | Argument >data types | Type >------------+----------------+------------------+------------------------------------------+------ > pg_catalog | regexp_replace | text | text, text, text > | func > pg_catalog | regexp_replace | text | text, text, text, >integer | func > pg_catalog | regexp_replace | text | text, text, text, >integer, integer | func > pg_catalog | regexp_replace | text | text, text, text, >integer, integer, text | func > pg_catalog | regexp_replace | text | text, text, text, >text | func Running my SQL in public, I get: An error occurred when executing the SQL command: select * from a,b where regex_replace(a.address,' ','','g') = regex_replace(b.address,' ','','g') ERROR: function regex_replace(text, unknown, unknown, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 27 Looks like rexexp_* need to be installed in each database I use... The question is how to use them from public where I get the above error...? >Just just them: > >select regexp_replace('Thomas', '.[mN]a.', 'M'); > regexp_replace >---------------- > ThM > > >> >> Sorry if this a newbie question... >> >>>> ncsbe=# \df "replace" >>>> List of functions >>>> Schema | Name | Result data type | Argument data types | Type >>>> ------------+---------+------------------+---------------------+------ >>>> pg_catalog | replace | text | text, text, text | func >>>> (1 row) >>>> >>>> ncsbe=# \df "regex" >>>> List of functions >>>> Schema | Name | Result data type | Argument data types | Type >>>> --------+------+------------------+---------------------+------ >>>> (0 rows) >>>> >>>> There are no regex* functions in /usr/share/postgresql/extension >>>> >>>> Thanks, >>>> Pierre >>>> >>>> >>> >