[Code: 0, SQL State: 0A000] when "typing" from pg_catalog

From: Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
Date: 2024-04-09 14:59:38
Message-ID: e037ed80-3a3a-4ed3-87e9-bb467c0a5b59@gelassene-pferde.biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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$;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2024-04-09 15:01:22 Re: Tracing libpq client: Only with PQtrace()?
Previous Message Greg Sabino Mullane 2024-04-09 14:50:11 Re: Tracing libpq client: Only with PQtrace()?