I fixed the array and error handling of the function.
create or replace function METHOD_CHECK()
returns trigger
language plpgsql
stable
as
$body$
declare
V_COUNT smallint;
begin
if TG_NARGS != 1 then
raise
exception
using
message = 'METHOD_CHECK expects the schema name to be
passed and nothing more! There have been passed ' ||
TG_NARGS || ' arguments.',
hint = 'Please check the trigger "' || TG_NAME ||
'" on table "' || TG_TABLE_NAME || '" in schema "' ||
TG_TABLE_SCHEMA || '".';
end if;
select COUNT(*) into V_COUNT
from INFORMATION_SCHEMA.SCHEMATA
where SCHEMA_NAME = TG_ARGV[0];
if V_COUNT != 1 then
raise
exception
using
message = 'Schema ' || coalesce('"' || TG_ARGV[0] || '"',
'ω/NULL') ||
'" could not be found!',
hint = 'Please check the trigger "' || TG_NAME ||
'" on table "' || TG_TABLE_NAME || '" in schema "' ||
TG_TABLE_SCHEMA || '".';
end if;
select COUNT(*) into V_COUNT
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_SCHEMA = TG_ARGV[0]
and ROUTINE_NAME = NEW.DB_ROUTINE_NAME;
if V_COUNT != 1 then
raise
exception
using
message = 'Routine "' || NEW.DB_ROUTINE_NAME ||
'" could not be found in schema "' ||
TG_ARGV[0] || '!',
hint = 'Install the routine beforehand.';
end if;
insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*);
return NULL;
end;
$body$;
Running this version, I get another proof that the term was literalised:
psql:common_calculation_method_insert.pg_sql:59: ERROR: Schema
"current_schema" could not be found!
HINT: Please check the trigger "calculation_method_br_iu" on table
"calculation_method" in schema "public".
CONTEXT: PL/pgSQL function method_check() line 20 at RAISE
Quoting Thiemo Kellner <thiemo@xxxxxxxxxxxxxxxxxxxx>:
Hi
I created a table with trigger and according trigger and trigger function as
drop table if exists CALCULATION_METHOD cascade;
create table CALCULATION_METHOD (ID
uuid
not null
default uuid_generate_v4(),
METHOD_NAME
text
not null,
DB_ROUTINE_NAME
name
not null,
ENTRY_PIT
timestamptz
not null
default transaction_timestamp(),
REMARKS
text,
constraint CALCULATION_METHOD_PK
primary key (ID),
constraint CALCULATION_METHOD_UQ
unique (DB_ROUTINE_NAME));
create or replace function METHOD_CHECK()
returns trigger
language plpgsql
stable
as
$body$
declare
V_COUNT smallint;
begin
if TG_NARGS != 1 then
raise
exception
using
message = 'METHOD_CHECK expects the schema name to
be passed and nothing more! There have been passed ' ||
TG_NARGS || ' arguments.',
hint = 'Please check the trigger "' || TG_NAME ||
'" on table "' || TG_TABLE_NAME || '" in
schema "' ||
TG_TABLE_SCHEMA || '".';
end if;
select COUNT(*) into V_COUNT
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_SCHEMA = TG_ARGV[1]
and ROUTINE_NAME = NEW.DB_ROUTINE_NAME;
if V_COUNT != 1 then
raise exception ' expects the schema name to be passed!';
end if;
insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*);
return NULL;
end;
$body$;
create trigger CALCULATION_METHOD_BR_IU
before insert on CALCULATION_METHOD
for each row
execute function METHOD_CHECK(current_schema);
Executing such, the string "current_schema" gets literalised, i.e.
single quoted:
norge=# \d calculation_method
Table "public.calculation_method"
Column | Type | Collation | Nullable |
Default
-----------------+--------------------------+-----------+----------+-------------------------
id | uuid | | not null |
uuid_generate_v4()
method_name | text | | not null |
db_routine_name | name | | not null |
entry_pit | timestamp with time zone | | not null |
transaction_timestamp()
remarks | text | | |
Indexes:
"calculation_method_pk" PRIMARY KEY, btree (id)
"calculation_method_uq" UNIQUE CONSTRAINT, btree (db_routine_name)
Triggers:
calculation_method_br_iu BEFORE INSERT ON calculation_method FOR
EACH ROW EXECUTE FUNCTION method_check('current_schema')
I am using
norge=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE
Linux) 10.2.1 20201028 [revision
a78cd759754c92cecbf235ac9b447dcdff6c6e2f], 64-bit
I strongly feel this is a bug, at least no intention at all from my
side. However, before filing a bug, I wanted to get your opinion on
that. Maybe it is just a problem of the openSUSE Tumbleweed
repository.
I would appreciate your two dimes. Kind regards
Thiemo
--
S/MIME Public Key:
https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Threema (Safer than WhatsApp): A76MKH3J
Handys: +41 78 947 36 21 | +49 1578 772 37 37
--
S/MIME Public Key: https://oc.gelassene-pferde.biz/index.php/s/eJuAUFONag6ofnH
Signal (Safer than WhatsApp): +49 1578 7723737
Threema (Safer than WhatsApp): A76MKH3J
Handys: +41 78 947 36 21 | +49 1578 772 37 37
