On 4/9/24 07:59, Thiemo Kellner wrote:
Hi
I have the following function code. When trying to install, it gives me
[Code: 0, SQL State: 0A000] FEHLER: Verweise auf andere Datenbanken
sind nicht implementiert: pg_catalog.pg_roles.rolname
Position: 298 [Script position: 334 - 361]
[Code: 0, SQL State: 0A000] ERROR: References to other databases are not
implemented: pg_catalog.pg_roles.rolname
Position: 298 [Script position: 334 - 361]
To the best of my knowledge, pg_catalog is a schema not a database, like
information_schema. Am I missing something? And why is it not allowed to
type from the catalogue?
I presume, this example is rather academic due to the name type.
PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader';
Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME.
Kind regards
Thiemo
create or replace function GRANT_SELECTS()
returns void
language plpgsql
as
$body$
declare
C_SCHEMA_NAME constant
INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME%type :=
'snowrunner';
-- C_ROLE_NAME constant name :=
C_ROLE_NAME constant PG_CATALOG.PG_ROLES.ROLNAME :=
'snowrunner_reader';
V_SQL_STATEMENT text;
begin
-- Check the existance of the schema
perform 1
from INFORMATION_SCHEMA.SCHEMATA
where SCHEMA_NAME = C_SCHEMA_NAME;
if not found then
raise exception 'Schema "%s" does not exist!', C_SCHEMA_NAME;
end if;
-- Check the existance of the role
perform 1
from PG_CATALOG.PG_ROLES
where ROLNAME = C_ROLE_NAME;
if not found then
raise exception 'Role "%s" does not exist!', C_ROLE_NAME;
end if;
-- Issue grants
V_SQL_STATEMENT := format('grant select on all tables in schema
%i to %i', C_SCHEMA_NAME, C_ROLE_NAME);
raise info '%', V_SQL_STATEMENT;
execute V_SQL_STATEMENT;
V_SQL_STATEMENT := format('grant select on all views in schema
%i to %i', C_SCHEMA_NAME, C_ROLE_NAME);
raise info '%', V_SQL_STATEMENT;
execute V_SQL_STATEMENT;
V_SQL_STATEMENT := format('grant select on all materialized
views in schema %i to %i', C_SCHEMA_NAME, C_ROLE_NAME);
raise info '%', V_SQL_STATEMENT;
execute V_SQL_STATEMENT;
commit;
return;
end;
$body$;
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
