On 1/3/21 1:44 PM, Thiemo Kellner wrote:
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.
Yeah, forgot about that.
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 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?
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$;
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
