Search Postgresql Archives

Possible trigger bug? function call argument literalised

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux