On 1/3/21 11:19 AM, Thiemo Kellner wrote:
> Quoting Adrian Klaver <adrian.klaver@aklaver.com>:
>
>> 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@aklaver.com