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