Quoting Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
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
