Re: Possible trigger bug? function call argument literalised

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