pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pf@pfortin.com
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Aren't regex_*() functions built-in?
Date: Sat, 11 Nov 2023 20:20:51 -0500
Message-ID: <20231111202051.5f1f1acc@pfortin.com> (raw)
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>
	<a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com>
	<20231111200417.539bebb0@pfortin.com>
	<152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.com>

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...?
>> 
>>   
>





view thread (12+ messages)  latest in thread

Message-ID: <20231111202051.5f1f1acc@pfortin.com>
Permalink:  ../20231111202051.5f1f1acc@pfortin.com/
Also on:    postgresql.org/message-id/20231111202051.5f1f1acc@pfortin.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: pf@pfortin.com, adrian.klaver@aklaver.com
  Subject: Re: Aren't regex_*() functions built-in?
  In-Reply-To: <20231111202051.5f1f1acc@pfortin.com>
  References: <20231111191214.60d182b2.pfortin@pfortin.com> <03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com> <20231111192548.55392ea9@pfortin.com> <a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com> <20231111200417.539bebb0@pfortin.com> <152909a1-f984-4ff5-bb9e-6d2820aca26b@aklaver.com> <20231111202051.5f1f1acc@pfortin.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox