Re: Possible trigger bug? function call argument literalised

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