Thread: Aren't regex_*() functions built-in?
Hi, PostgreSQL 15.4 on x86_64-mageia-linux-gnu, compiled by gcc (Mageia 12.3.0-3.mga9) 12.3.0, 64-bit (the distro which can't figure out how to provide pgAdmin4) Aren't all the functions listed in https://www.postgresql.org/docs/current/functions-string.html assumed to be included in a base installation? I'm able to use replace(); but with regex_replace(), I get: 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. 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
On Saturday, November 11, 2023, <pf@pfortin.com> wrote:
Hi,
PostgreSQL 15.4 on x86_64-mageia-linux-gnu, compiled by gcc (Mageia
12.3.0-3.mga9) 12.3.0, 64-bit
(the distro which can't figure out how to provide pgAdmin4)
Aren't all the functions listed in
https://www.postgresql.org/docs/current/functions-string. html
assumed to be included in a base installation?
I'm able to use replace(); but with regex_replace(), I get:
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.
We abbreviate with a ending “p”. regexp_*
David J.
On 11/11/23 16:12, pf@pfortin.com wrote: > Hi, > > PostgreSQL 15.4 on x86_64-mageia-linux-gnu, compiled by gcc (Mageia > 12.3.0-3.mga9) 12.3.0, 64-bit > (the distro which can't figure out how to provide pgAdmin4) > > Aren't all the functions listed in > https://www.postgresql.org/docs/current/functions-string.html > assumed to be included in a base installation? > > I'm able to use replace(); but with regex_replace(), I get: > 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. Probably because it is spelled regexp_replace (). > > 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 > > -- Adrian Klaver adrian.klaver@aklaver.com
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
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
>>>
>>>
>>
--
Adrian Klaver
adrian.klaver@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
>>>>
>>>>
>>>
>
On 11/11/23 17:04, pf@pfortin.com wrote:
> 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')
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@aklaver.com
On Sat, 11 Nov 2023 17:10:29 -0800 Adrian Klaver wrote: >On 11/11/23 17:04, pf@pfortin.com wrote: >> 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') > >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...? >> >> >
> On Nov 11, 2023, at 17:20, pf@pfortin.com wrote: > 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..? The permission error isn't on the function, but on the table "a". The RO user doesn't have the appropriate permissions onit.
On Saturday, November 11, 2023, <pf@pfortin.com> wrote:
I had no idea functions need permissions... GRANT EXTENSION..?
You really need to get away from thinking anything you are doing has to do with extensions. As you noted the function you found is in the core part of the documentation so it isn’t an extension.
David J.
On 11/11/23 17:20, pf@pfortin.com wrote: > On Sat, 11 Nov 2023 17:10:29 -0800 Adrian Klaver wrote: > > > 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..? 1) Functions do have permissions: https://www.postgresql.org/docs/current/ddl-priv.html EXECUTE Allows calling a function or procedure, including use of any operators that are implemented on top of the function. This is the only type of privilege that is applicable to functions and procedures. 2) This is not the problem here select * from a,b; would result in the same error. 3) This has nothing to do with extensions. > >>> 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@aklaver.com
I need to follow my own advice: slow-down and you'll go faster... Logged in as "postgres" (superuser). I see regexp_replace(); but not if logged in as a read-only user... So this is a permissions issue... I just discovered that a RO user with only SELECT permision can run a query using regexp_replace... Your other message just arrived: >1) Functions do have permissions: > >https://www.postgresql.org/docs/current/ddl-priv.html > >EXECUTE > > Allows calling a function or procedure, including use of any >operators that are implemented on top of the function. This is the only >type of privilege that is applicable to functions and procedures. Interesting... I'm connected as a user which is part of the "ro_users" group which has only SELECT permission, nothing else; yet, I can run this query (finally :) now that the tables are granted "ro_users" SELECT... my bad on that... but: select * from a,b where regexp_replace(a.address,' ','','g') = regexp_replace(b.address,' ','','g'); works. Apparently, EXECUTE is not required: to be sure, I ran this on both tables: SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='a'; Which respond with: ro_users SELECT If EXECUTE is really supposed to be required; is it a bug that I finally got regexp_replace() to work...? So I was failing due to permissions (there were none on the tables); but applying only SELECT allowed it to work without EXECUTE which should be required according to the manual...? This is beyond my current skills which greatly advance tonight... THANKS!! Pierre
On Saturday, November 11, 2023, <pf@pfortin.com> wrote:
If EXECUTE is really supposed to be required; is it a bug that I
finally got regexp_replace() to work...?
The PUBLIC pseudo-role is granted permission, at the time of the function’s creation (i.e., as a default privilege) to execute all functions in the database. All roles inherit the grants given to PUBLIC.
David J.