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

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