Re: Aren't regex_*() functions built-in?

On Sat, 11 Nov 2023 17:10:29 -0800 Adrian Klaver wrote:

>On 11/11/23 17:04, pf(at)pfortin(dot)com wrote:
>> On Sat, 11 Nov 2023 16:53:01 -0800 Adrian Klaver wrote:
>>
>>> On 11/11/23 16:25, pf(at)pfortin(dot)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...?
>>
>>
>