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'.
^
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...?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
