On 1/3/21 11:19 AM, Thiemo Kellner wrote:
Quoting Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
Can you provide an outline form of what you are trying to accomplish?
Hm, making myself understood. ;-) So from the very beginning.
There is the concept of growing degree days
Familiar with it, I have worked in farming(outdoor and
indoor(greenhouse)) industries.
(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.
More below.
Let stay at plants. It is basically the daily average temperature. As
plants do not grow below a certain, plant specific temperature, this
base temperature gets substracted from the average. Usually plants grow
faster the warmer it is. But only to a certain temperature above which
the growth rate remains. However, the arithmetic temperature average is
not the most accurate approximation, so there are other methods to
calculate the amount of energy available to grow. To cut a long story
short, I implemented several such methods as pg/plsql functions. And I
implement a datamodel, where plants get connected to the amount of
growing degree days to mature. As this value is method dependent, all
the plant values get the method used to calculate it, assigned too. To
prevent the assignement of imaginary methods, I setup foreign key
relation. Unfortunately, it is not allowed to reference the catalog
tables, so I put up a dimension table. In order to prevent imaginary
entries there, I want to check the existence of the entry-to-be as
installed function (information_schema.routines). I wanted to have a
general solution for the check to facilitate reuse of the method_check
trigger function.
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?
If that is the case my previous suggestion of finding the CURRENT_SCHEMA
inside the function would work?
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;
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
