Re: Possible trigger bug? function call argument literalised

Quoting Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> 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