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 1r1z9r-00EZaM-PP for pgsql-general@arkaria.postgresql.org; Sun, 12 Nov 2023 01:21:03 +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 1r1z9p-009AvL-CR for pgsql-general@arkaria.postgresql.org; Sun, 12 Nov 2023 01:21:01 +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 1r1z9o-009Aus-ML for pgsql-general@lists.postgresql.org; Sun, 12 Nov 2023 01:21:01 +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 1r1z9j-0058bN-C9 for pgsql-general@postgresql.org; Sun, 12 Nov 2023 01:20:56 +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 3AC1KpVm057016 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NOT); Sun, 12 Nov 2023 01:20:52 GMT Date: Sat, 11 Nov 2023 20:20:51 -0500 From: pf@pfortin.com To: Adrian Klaver Cc: pgsql-general Subject: Re: Aren't regex_*() functions built-in? Message-ID: <20231111202051.5f1f1acc@pfortin.com> In-Reply-To: <152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.com> References: <20231111191214.60d182b2.pfortin@pfortin.com> <03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com> <20231111192548.55392ea9@pfortin.com> <20231111200417.539bebb0@pfortin.com> <152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.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 3AC1KpVm057016 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=Hk4Xv0Gp7S4Ebj4Ide8DFVMWGLu2REwll0cGmbPCXwA=; b=OQKaiBwK2G71q5mHqn0+qvJ4CA6VJ7pv9mn43YRZ+wCjocxYS01YGlXgoyrAD8bWlvn5nTGRYq9AE9HkyD0c0frTwlIkKgnpicBLEYSyqdqGmfDNHE4UXs1IWg6ZTMzt4dZzWj+Scpb6ZmBPym7kziFzF7BR8I0ZABjIoHwSUJs= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sat, 11 Nov 2023 17:10:29 -0800 Adrian Klaver wrote: >On 11/11/23 17:04, pf@pfortin.com wrote: >> 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') > >Again because it is regexp_replace not regex_replace. NOTE the 'p'. > ^ Actually, it's more eusbtle... I can make it work as "postgres"; but not as a RO user (SELECT only): An error occurred when executing the SQL command: select * from a,b where regexp_replace(a.address,' ','','g') = regexp_replace(b.address,' ','','g') ERROR: permission denied for table a 1 statement failed. I had no idea functions need permissions... GRANT EXTENSION..? >> 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...? >> >> >