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