From: pf@pfortin.com
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: Aren't regex_*() functions built-in?
Date: Sat, 11 Nov 2023 20:04:17 -0500
Message-ID: <20231111200417.539bebb0@pfortin.com> (raw)
In-Reply-To: <a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com>
References: <20231111191214.60d182b2.pfortin@pfortin.com>
<03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com>
<20231111192548.55392ea9@pfortin.com>
<a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com>
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
>>>>
>>>>
>>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: pf@pfortin.com, adrian.klaver@aklaver.com
Subject: Re: Aren't regex_*() functions built-in?
In-Reply-To: <20231111200417.539bebb0@pfortin.com>
References: <20231111191214.60d182b2.pfortin@pfortin.com> <03377896-6a6c-435f-8a42-6f617f1adf22@aklaver.com> <20231111192548.55392ea9@pfortin.com> <a479d6c0-b7eb-46ab-bb82-fc23774556da@aklaver.com> <20231111200417.539bebb0@pfortin.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox