Aren't regex_*() functions built-in?

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

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

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 on it.

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.