Possible trigger bug? function call argument literalised

Поиск
Список
Период
Сортировка
От Thiemo Kellner
Тема Possible trigger bug? function call argument literalised
Дата
Msg-id 20210103180840.Horde.Fe4qNP3Uviz_9c6aPfxBTS2@webmail.gelassene-pferde.biz
обсуждение исходный текст
Ответы Re: Possible trigger bug? function call argument literalised  (Rob Sargent <robjsargent@gmail.com>)
Re: Possible trigger bug? function call argument literalised  (Thiemo Kellner <thiemo@gelassene-pferde.biz>)
Re: Possible trigger bug? function call argument literalised  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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




В списке pgsql-general по дате отправления:

Предыдущее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Max# of tablespaces
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Max# of tablespaces