Re: Possible trigger bug? function call argument literalised

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(at)gelassene-pferde(dot)biz>:

> 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