On Sat, 11 Nov 2023 17:10:29 -0800 Adrian Klaver wrote: >On 11/11/23 17:04, pf@xxxxxxxxxxx wrote: >> On Sat, 11 Nov 2023 16:53:01 -0800 Adrian Klaver wrote: >> >>> On 11/11/23 16:25, pf@xxxxxxxxxxx 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...? >> >> >
