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