Обсуждение: [MASSMAIL][Code: 0, SQL State: 0A000] when "typing" from pg_catalog
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]
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.
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$;
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@aklaver.com
Thanks for taking this up. Am 09.04.2024 um 17:09 schrieb Adrian Klaver: > On 4/9/24 07:59, Thiemo Kellner wrote: > [Code: 0, SQL State: 0A000] ERROR: References to other databases are not > implemented: pg_catalog.pg_roles.rolname > Position: 298 [Script position: 334 - 361] > > PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader'; > > Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME. Yes, obviously, but why? With the information_schema view all is fine. And, I suppose, with all other objects in other schemas of the same database too.
On 4/9/24 08:12, Thiemo Kellner wrote: > Thanks for taking this up. > > Am 09.04.2024 um 17:09 schrieb Adrian Klaver: >> On 4/9/24 07:59, Thiemo Kellner wrote: >> [Code: 0, SQL State: 0A000] ERROR: References to other databases are >> not implemented: pg_catalog.pg_roles.rolname >> Position: 298 [Script position: 334 - 361] >> >> PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader'; >> >> Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME. > > Yes, obviously, but why? With the information_schema view all is fine. > And, I suppose, with all other objects in other schemas of the same > database too. Because you did not do?: PG_CATALOG.PG_ROLES.ROLNAME%type -- Adrian Klaver adrian.klaver@aklaver.com
Am 09.04.2024 um 17:18 schrieb Adrian Klaver: > Because you did not do?: > > PG_CATALOG.PG_ROLES.ROLNAME%type Oh, right. Sorry. What an oversight.
Am 09.04.2024 um 17:18 schrieb Adrian Klaver: > Because you did not do?: > > PG_CATALOG.PG_ROLES.ROLNAME%type Thanks
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 4/9/24 08:12, Thiemo Kellner wrote:
>>> PG_CATALOG.PG_ROLES.ROLNAME := 'snowrunner_reader';
>>>
>>> Is being seen as DB_NAME.TABLE_NAME.COLUMN_NAME.
No, it's being seen as DB_NAME.SCHEMA_NAME.TYPE_NAME.
> Because you did not do?:
> PG_CATALOG.PG_ROLES.ROLNAME%type
Exactly. The %type bit is important.
regards, tom lane