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@gelassene-pferde.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