Re: Possible trigger bug? function call argument literalised - Mailing list pgsql-general

> On Jan 3, 2021, at 10:08 AM, Thiemo Kellner <thiemo@gelassene-pferde.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
getyour 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?

>