Re: Possible trigger bug? function call argument literalised

> On Jan 3, 2021, at 10:08 AM, Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz> wrote:
>
> 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
>
>
The function definition doesn’t name any parameters?

>