public inbox for [email protected]
help / color / mirror / Atom feed[Code: 0, SQL State: 0A000] when "typing" from pg_catalog
7+ messages / 3 participants
[nested] [flat]
* [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
@ 2024-04-09 14:59 Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Thiemo Kellner @ 2024-04-09 14:59 UTC (permalink / raw)
To: [email protected] <[email protected]>
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$;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
@ 2024-04-09 15:09 ` Adrian Klaver <[email protected]>
2024-04-09 15:12 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2024-04-09 15:09 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; [email protected] <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
@ 2024-04-09 15:12 ` Thiemo Kellner <[email protected]>
2024-04-09 15:18 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Thiemo Kellner @ 2024-04-09 15:12 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
2024-04-09 15:12 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
@ 2024-04-09 15:18 ` Adrian Klaver <[email protected]>
2024-04-09 16:48 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 16:49 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 17:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Tom Lane <[email protected]>
0 siblings, 3 replies; 7+ messages in thread
From: Adrian Klaver @ 2024-04-09 15:18 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; [email protected] <[email protected]>
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
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
2024-04-09 15:12 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:18 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
@ 2024-04-09 16:48 ` Thiemo Kellner <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Thiemo Kellner @ 2024-04-09 16:48 UTC (permalink / raw)
To: [email protected] <[email protected]>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
2024-04-09 15:12 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:18 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
@ 2024-04-09 16:49 ` Thiemo Kellner <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Thiemo Kellner @ 2024-04-09 16:49 UTC (permalink / raw)
To: [email protected] <[email protected]>
Am 09.04.2024 um 17:18 schrieb Adrian Klaver:
> Because you did not do?:
>
> PG_CATALOG.PG_ROLES.ROLNAME%type
Thanks
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
2024-04-09 15:12 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:18 ` Re: [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Adrian Klaver <[email protected]>
@ 2024-04-09 17:09 ` Tom Lane <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2024-04-09 17:09 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: Thiemo Kellner <[email protected]>; [email protected] <[email protected]>
Adrian Klaver <[email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-04-09 17:09 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-09 14:59 [Code: 0, SQL State: 0A000] when "typing" from pg_catalog Thiemo Kellner <[email protected]>
2024-04-09 15:09 ` Adrian Klaver <[email protected]>
2024-04-09 15:12 ` Thiemo Kellner <[email protected]>
2024-04-09 15:18 ` Adrian Klaver <[email protected]>
2024-04-09 16:48 ` Thiemo Kellner <[email protected]>
2024-04-09 16:49 ` Thiemo Kellner <[email protected]>
2024-04-09 17:09 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox