Re: Aren't regex_*() functions built-in? - Mailing list pgsql-general

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