pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedAren't regex_*() functions built-in?
12+ messages / 4 participants
[nested] [flat]
* Aren't regex_*() functions built-in?
@ 2023-11-12 00:12 pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? David G. Johnston <david.g.johnston@gmail.com>
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 12+ messages in thread
From: pf@pfortin.com @ 2023-11-12 00:12 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 00:16 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2023-11-12 00:16 UTC (permalink / raw)
To: pf@pfortin.com <pf@pfortin.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 00:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2023-11-12 00:16 UTC (permalink / raw)
To: pf@pfortin.com; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-11-12 00:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
0 siblings, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2023-11-12 00:53 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-11-12 01:04 ` pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 1 reply; 12+ messages in thread
From: pf@pfortin.com @ 2023-11-12 01:04 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
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
>>>>
>>>>
>>>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 01:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
0 siblings, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2023-11-12 01:10 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-11-12 01:20 ` pf@pfortin.com
2023-11-12 01:21 ` Re: Aren't regex_*() functions built-in? Christophe Pettus <xof@thebuild.com>
2023-11-12 01:26 ` Re: Aren't regex_*() functions built-in? David G. Johnston <david.g.johnston@gmail.com>
2023-11-12 01:37 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 02:05 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
0 siblings, 4 replies; 12+ messages in thread
From: pf@pfortin.com @ 2023-11-12 01:20 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
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...?
>>
>>
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 01:21 ` Christophe Pettus <xof@thebuild.com>
3 siblings, 0 replies; 12+ messages in thread
From: Christophe Pettus @ 2023-11-12 01:21 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
> 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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 01:26 ` David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2023-11-12 01:26 UTC (permalink / raw)
To: pf@pfortin.com <pf@pfortin.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 01:37 ` Adrian Klaver <adrian.klaver@aklaver.com>
3 siblings, 0 replies; 12+ messages in thread
From: Adrian Klaver @ 2023-11-12 01:37 UTC (permalink / raw)
To: pf@pfortin.com; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 02:05 ` pf@pfortin.com
2023-11-12 02:11 ` Re: Aren't regex_*() functions built-in? David G. Johnston <david.g.johnston@gmail.com>
3 siblings, 1 reply; 12+ messages in thread
From: pf@pfortin.com @ 2023-11-12 02:05 UTC (permalink / raw)
To: ; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Aren't regex_*() functions built-in?
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 01:10 ` Re: Aren't regex_*() functions built-in? Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 02:05 ` Re: Aren't regex_*() functions built-in? pf@pfortin.com
@ 2023-11-12 02:11 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: David G. Johnston @ 2023-11-12 02:11 UTC (permalink / raw)
To: pf@pfortin.com <pf@pfortin.com>; +Cc: pgsql-general
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.
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2023-11-12 02:11 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-11-12 00:12 Aren't regex_*() functions built-in? pf@pfortin.com
2023-11-12 00:16 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-12 00:16 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 00:53 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:04 ` pf@pfortin.com
2023-11-12 01:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 01:20 ` pf@pfortin.com
2023-11-12 01:21 ` Christophe Pettus <xof@thebuild.com>
2023-11-12 01:26 ` David G. Johnston <david.g.johnston@gmail.com>
2023-11-12 01:37 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-11-12 02:05 ` pf@pfortin.com
2023-11-12 02:11 ` David G. Johnston <david.g.johnston@gmail.com>
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