[MASSMAIL][Code: 0, SQL State: 0A000] when "typing" from pg_catalog
От | Thiemo Kellner |
---|---|
Тема | [MASSMAIL][Code: 0, SQL State: 0A000] when "typing" from pg_catalog |
Дата | |
Msg-id | e037ed80-3a3a-4ed3-87e9-bb467c0a5b59@gelassene-pferde.biz обсуждение исходный текст |
Ответы |
Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
|
Список | pgsql-general |
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$;
В списке pgsql-general по дате отправления: