Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver <adrian.klaver@aklaver.com>:

> Familiar with it, I have worked in farming(outdoor and  
> indoor(greenhouse)) industries.

Cool

>> (https://en.wikipedia.org/wiki/Growing_degree-day). It is a measure  
>> for energy an organism can consume in a specific day for its  
>> development.
>
> Also used to anticipate pest pressure on plants.

:-)

> So if I am following you are trying to set up a dynamic FK like  
> process to INFORMATION_SCHEMA.ROUTINES on INSERT to  
> CALCULATION_METHOD?

Perfectly summarised.

> If that is the case my previous suggestion of finding the  
> CURRENT_SCHEMA inside the function would work?

Actually, I did not follow it. I decided to try to go for TG_TABLE_SCHEMA.

> Personally I would create a script the built and populated  
> CALCULATION_METHOD table as you added the functions to the database  
> and schema. So:
>
> BEGIN;
> CREATE TABLE CALCULATION_METHOD ...
>
> CREATE the_schema.some_dd_fnc();
>
> INSERT INTO CALCULATION_METHOD VALUES(<required_fields>)
>
> --Where db_routine_name would be set to the function name.
> ...
>
> COMMIT;

To me, it does not seem to have FK function. I figure, I also could

insert into CALCULATION_METHOD (DB_ROUTINE_NAME) select ROUTINE_NAME  
from INFORMATION_SCHEMA.ROUTINES;

But again, I had no FK functionality and I would have only the routine  
name. Remarks and other attributes would need to be maintained in  
extra steps.

So, I implemented a non-general solution.

create or replace function METHOD_CHECK()
   returns trigger
   language plpgsql
   volatile
   as
$body$
     declare
         V_COUNT     smallint;
     begin
         select COUNT(*) into V_COUNT
           from INFORMATION_SCHEMA.ROUTINES
          where ROUTINE_SCHEMA   = TG_TABLE_SCHEMA
            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_TABLE_SCHEMA || '!',
                 hint = 'Install the routine beforehand.';
         end if;
         return NEW; -- If NULL was returned, the row would get skipped!
     end;
$body$;




-- 
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