Re: Possible trigger bug? function call argument literalised

On 1/3/21 1:44 PM, Thiemo Kellner wrote:
>

>> 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.

Yeah, forgot about that.

>
>> 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 is the below still only going to fire on INSERT?

If so it will not deal with functions that disappear after the INSERT,
which in the end makes it similar to my suggestion:) The point being you
are taking a snapshot in time and hoping that holds going forward. Of
course when a calculation fails because the function is no longer there
or has changed you will know a change has occurred. Is there some
process to deal with the preceding?

>
> 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$;
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com