Re: Possible trigger bug? function call argument literalised

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

> On 1/3/21 1:44 PM, Thiemo Kellner wrote:

> So is the below still only going to fire on INSERT?
>
> If so it will not deal with functions that disappear after the  
> INSERT, which in the end makes it similar to my suggestion:) The  
> point being you are taking a snapshot in time and hoping that holds  
> going forward. Of course when a calculation fails because the  
> function is no longer there or has changed you will know a change  
> has occurred. Is there some process to deal with the preceding?

Yes insert only, I reckon there is no way to automatically handle  
deletes of functions - unless I could install a trigger on the very  
catalogue table which I will not consider even as last resort. I also  
discarded the update because I am only interested in the presence  
check. So, if my dimension table changes some payload attribute  
values, I do not need to propagate this change anywhere. On the other  
hand, if someone changes the value of DB_ROUTINE_NAME, I better check.

It is a project of my own. There is no process defined. ;-)

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