Thread: Possible trigger bug? function call argument literalised
Hi
I created a table with trigger and according trigger and trigger function as
drop table if exists CALCULATION_METHOD cascade;
create table CALCULATION_METHOD (ID
uuid
not null
default uuid_generate_v4(),
METHOD_NAME
text
not null,
DB_ROUTINE_NAME
name
not null,
ENTRY_PIT
timestamptz
not null
default transaction_timestamp(),
REMARKS
text,
constraint CALCULATION_METHOD_PK
primary key (ID),
constraint CALCULATION_METHOD_UQ
unique (DB_ROUTINE_NAME));
create or replace function METHOD_CHECK()
returns trigger
language plpgsql
stable
as
$body$
declare
V_COUNT smallint;
begin
if TG_NARGS != 1 then
raise
exception
using
message = 'METHOD_CHECK expects the schema name to be
passed and nothing more! There have been passed ' ||
TG_NARGS || ' arguments.',
hint = 'Please check the trigger "' || TG_NAME ||
'" on table "' || TG_TABLE_NAME || '" in schema "' ||
TG_TABLE_SCHEMA || '".';
end if;
select COUNT(*) into V_COUNT
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_SCHEMA = TG_ARGV[1]
and ROUTINE_NAME = NEW.DB_ROUTINE_NAME;
if V_COUNT != 1 then
raise exception ' expects the schema name to be passed!';
end if;
insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*);
return NULL;
end;
$body$;
create trigger CALCULATION_METHOD_BR_IU
before insert on CALCULATION_METHOD
for each row
execute function METHOD_CHECK(current_schema);
Executing such, the string "current_schema" gets literalised, i.e.
single quoted:
norge=# \d calculation_method
Table "public.calculation_method"
Column | Type | Collation | Nullable |
Default
-----------------+--------------------------+-----------+----------+-------------------------
id | uuid | | not null |
uuid_generate_v4()
method_name | text | | not null |
db_routine_name | name | | not null |
entry_pit | timestamp with time zone | | not null |
transaction_timestamp()
remarks | text | | |
Indexes:
"calculation_method_pk" PRIMARY KEY, btree (id)
"calculation_method_uq" UNIQUE CONSTRAINT, btree (db_routine_name)
Triggers:
calculation_method_br_iu BEFORE INSERT ON calculation_method FOR
EACH ROW EXECUTE FUNCTION method_check('current_schema')
I am using
norge=# select version();
version
----------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE
Linux) 10.2.1 20201028 [revision
a78cd759754c92cecbf235ac9b447dcdff6c6e2f], 64-bit
I strongly feel this is a bug, at least no intention at all from my
side. However, before filing a bug, I wanted to get your opinion on
that. Maybe it is just a problem of the openSUSE Tumbleweed repository.
I would appreciate your two dimes. Kind regards
Thiemo
--
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
> On Jan 3, 2021, at 10:08 AM, Thiemo Kellner <thiemo@gelassene-pferde.biz> wrote:
>
> Hi
>
> I created a table with trigger and according trigger and trigger function as
>
> drop table if exists CALCULATION_METHOD cascade;
> create table CALCULATION_METHOD (ID
> uuid
> not null
> default uuid_generate_v4(),
> METHOD_NAME
> text
> not null,
> DB_ROUTINE_NAME
> name
> not null,
> ENTRY_PIT
> timestamptz
> not null
> default transaction_timestamp(),
> REMARKS
> text,
> constraint CALCULATION_METHOD_PK
> primary key (ID),
> constraint CALCULATION_METHOD_UQ
> unique (DB_ROUTINE_NAME));
>
> create or replace function METHOD_CHECK()
> returns trigger
> language plpgsql
> stable
> as
> $body$
> declare
> V_COUNT smallint;
> begin
> if TG_NARGS != 1 then
> raise
> exception
> using
> message = 'METHOD_CHECK expects the schema name to be passed and nothing more! There have been passed
'||
> TG_NARGS || ' arguments.',
> hint = 'Please check the trigger "' || TG_NAME ||
> '" on table "' || TG_TABLE_NAME || '" in schema "' ||
> TG_TABLE_SCHEMA || '".';
> end if;
> select COUNT(*) into V_COUNT
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_SCHEMA = TG_ARGV[1]
> and ROUTINE_NAME = NEW.DB_ROUTINE_NAME;
> if V_COUNT != 1 then
> raise exception ' expects the schema name to be passed!';
> end if;
> insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*);
> return NULL;
> end;
> $body$;
>
> create trigger CALCULATION_METHOD_BR_IU
> before insert on CALCULATION_METHOD
> for each row
> execute function METHOD_CHECK(current_schema);
>
>
> Executing such, the string "current_schema" gets literalised, i.e. single quoted:
> norge=# \d calculation_method
> Table "public.calculation_method"
> Column | Type | Collation | Nullable | Default
> -----------------+--------------------------+-----------+----------+-------------------------
> id | uuid | | not null | uuid_generate_v4()
> method_name | text | | not null |
> db_routine_name | name | | not null |
> entry_pit | timestamp with time zone | | not null | transaction_timestamp()
> remarks | text | | |
> Indexes:
> "calculation_method_pk" PRIMARY KEY, btree (id)
> "calculation_method_uq" UNIQUE CONSTRAINT, btree (db_routine_name)
> Triggers:
> calculation_method_br_iu BEFORE INSERT ON calculation_method FOR EACH ROW EXECUTE FUNCTION
method_check('current_schema')
>
>
> I am using
> norge=# select version();
> version
>
----------------------------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 12.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE Linux) 10.2.1 20201028 [revision
a78cd759754c92cecbf235ac9b447dcdff6c6e2f],64-bit
>
> I strongly feel this is a bug, at least no intention at all from my side. However, before filing a bug, I wanted to
getyour opinion on that. Maybe it is just a problem of the openSUSE Tumbleweed repository.
>
> I would appreciate your two dimes. Kind regards
>
> Thiemo
>
> --
> 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
>
>
The function definition doesn’t name any parameters?
>
I fixed the array and error handling of the function.
create or replace function METHOD_CHECK()
returns trigger
language plpgsql
stable
as
$body$
declare
V_COUNT smallint;
begin
if TG_NARGS != 1 then
raise
exception
using
message = 'METHOD_CHECK expects the schema name to be
passed and nothing more! There have been passed ' ||
TG_NARGS || ' arguments.',
hint = 'Please check the trigger "' || TG_NAME ||
'" on table "' || TG_TABLE_NAME || '" in schema "' ||
TG_TABLE_SCHEMA || '".';
end if;
select COUNT(*) into V_COUNT
from INFORMATION_SCHEMA.SCHEMATA
where SCHEMA_NAME = TG_ARGV[0];
if V_COUNT != 1 then
raise
exception
using
message = 'Schema ' || coalesce('"' || TG_ARGV[0] || '"',
'ω/NULL') ||
'" could not be found!',
hint = 'Please check the trigger "' || TG_NAME ||
'" on table "' || TG_TABLE_NAME || '" in schema "' ||
TG_TABLE_SCHEMA || '".';
end if;
select COUNT(*) into V_COUNT
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_SCHEMA = TG_ARGV[0]
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_ARGV[0] || '!',
hint = 'Install the routine beforehand.';
end if;
insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*);
return NULL;
end;
$body$;
Running this version, I get another proof that the term was literalised:
psql:common_calculation_method_insert.pg_sql:59: ERROR: Schema
"current_schema" could not be found!
HINT: Please check the trigger "calculation_method_br_iu" on table
"calculation_method" in schema "public".
CONTEXT: PL/pgSQL function method_check() line 20 at RAISE
Quoting Thiemo Kellner <thiemo@gelassene-pferde.biz>:
> Hi
>
> I created a table with trigger and according trigger and trigger function as
>
> drop table if exists CALCULATION_METHOD cascade;
> create table CALCULATION_METHOD (ID
> uuid
> not null
> default uuid_generate_v4(),
> METHOD_NAME
> text
> not null,
> DB_ROUTINE_NAME
> name
> not null,
> ENTRY_PIT
> timestamptz
> not null
> default transaction_timestamp(),
> REMARKS
> text,
> constraint CALCULATION_METHOD_PK
> primary key (ID),
> constraint CALCULATION_METHOD_UQ
> unique (DB_ROUTINE_NAME));
>
> create or replace function METHOD_CHECK()
> returns trigger
> language plpgsql
> stable
> as
> $body$
> declare
> V_COUNT smallint;
> begin
> if TG_NARGS != 1 then
> raise
> exception
> using
> message = 'METHOD_CHECK expects the schema name to
> be passed and nothing more! There have been passed ' ||
> TG_NARGS || ' arguments.',
> hint = 'Please check the trigger "' || TG_NAME ||
> '" on table "' || TG_TABLE_NAME || '" in
> schema "' ||
> TG_TABLE_SCHEMA || '".';
> end if;
> select COUNT(*) into V_COUNT
> from INFORMATION_SCHEMA.ROUTINES
> where ROUTINE_SCHEMA = TG_ARGV[1]
> and ROUTINE_NAME = NEW.DB_ROUTINE_NAME;
> if V_COUNT != 1 then
> raise exception ' expects the schema name to be passed!';
> end if;
> insert into TG_TABLE_SCHEMA.TG_TABLE_NAME values (NEW.*);
> return NULL;
> end;
> $body$;
>
> create trigger CALCULATION_METHOD_BR_IU
> before insert on CALCULATION_METHOD
> for each row
> execute function METHOD_CHECK(current_schema);
>
>
> Executing such, the string "current_schema" gets literalised, i.e.
> single quoted:
> norge=# \d calculation_method
> Table "public.calculation_method"
> Column | Type | Collation | Nullable |
> Default
> -----------------+--------------------------+-----------+----------+-------------------------
> id | uuid | | not null |
> uuid_generate_v4()
> method_name | text | | not null |
> db_routine_name | name | | not null |
> entry_pit | timestamp with time zone | | not null |
> transaction_timestamp()
> remarks | text | | |
> Indexes:
> "calculation_method_pk" PRIMARY KEY, btree (id)
> "calculation_method_uq" UNIQUE CONSTRAINT, btree (db_routine_name)
> Triggers:
> calculation_method_br_iu BEFORE INSERT ON calculation_method FOR
> EACH ROW EXECUTE FUNCTION method_check('current_schema')
>
>
> I am using
> norge=# select version();
> version
>
----------------------------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 12.5 on x86_64-suse-linux-gnu, compiled by gcc (SUSE
> Linux) 10.2.1 20201028 [revision
> a78cd759754c92cecbf235ac9b447dcdff6c6e2f], 64-bit
>
> I strongly feel this is a bug, at least no intention at all from my
> side. However, before filing a bug, I wanted to get your opinion on
> that. Maybe it is just a problem of the openSUSE Tumbleweed
> repository.
>
> I would appreciate your two dimes. Kind regards
>
> Thiemo
>
> --
> 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
--
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
Thiemo Kellner <thiemo@gelassene-pferde.biz> writes:
> create trigger CALCULATION_METHOD_BR_IU
> before insert on CALCULATION_METHOD
> for each row
> execute function METHOD_CHECK(current_schema);
> Executing such, the string "current_schema" gets literalised, i.e.
> single quoted:
Yup, per the CREATE TRIGGER documentation [1]:
arguments
An optional comma-separated list of arguments to be provided to the
function when the trigger is executed. The arguments are literal
string constants. Simple names and numeric constants can be written
here, too, but they will all be converted to strings. Please check the
description of the implementation language of the trigger function to
find out how these arguments can be accessed within the function; it
might be different from normal function arguments.
> I strongly feel this is a bug,
It's operating as designed. There might be scope for a feature
improvement here, but it'd be a far-from-trivial task, with probably
a lot of ensuing compatibility breakage.
regards, tom lane
[1] https://www.postgresql.org/docs/current/sql-createtrigger.html
Quoting Rob Sargent <robjsargent@gmail.com>: >> >> > The function definition doesn’t name any parameters? > >> Nope, trigger functions cannot, according to documentation. Parameters can be passed as list/array of values though. I have no clue about why this needs to be so awful/awesome. -- 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
Quoting Tom Lane <tgl@sss.pgh.pa.us>: > Thiemo Kellner <thiemo@gelassene-pferde.biz> writes: >> create trigger CALCULATION_METHOD_BR_IU >> before insert on CALCULATION_METHOD >> for each row >> execute function METHOD_CHECK(current_schema); > >> Executing such, the string "current_schema" gets literalised, i.e. >> single quoted: > > Yup, per the CREATE TRIGGER documentation [1]: > > arguments > > An optional comma-separated list of arguments to be provided to the > function when the trigger is executed. The arguments are literal > string constants. Simple names and numeric constants can be written > here, too, but they will all be converted to strings. Please check the > description of the implementation language of the trigger function to > find out how these arguments can be accessed within the function; it > might be different from normal function arguments. > >> I strongly feel this is a bug, > > It's operating as designed. There might be scope for a feature > improvement here, but it'd be a far-from-trivial task, with probably > a lot of ensuing compatibility breakage. > Oh, thanks! I did not read careful enough. I could not imagine such a behaviour to be intentional. Well, I guess, I can put the correct schema at installation, but would have liked to have a more general approach. Furthermore, I think this also implies that installation can only be done by psql. :-s -- 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
On 1/3/21 9:45 AM, Thiemo Kellner wrote:
>
> Quoting Tom Lane <tgl@sss.pgh.pa.us>:
>
>> Thiemo Kellner <thiemo@gelassene-pferde.biz> writes:
>>> create trigger CALCULATION_METHOD_BR_IU
>>> before insert on CALCULATION_METHOD
>>> for each row
>>> execute function METHOD_CHECK(current_schema);
>>
>>> Executing such, the string "current_schema" gets literalised, i.e.
>>> single quoted:
>>
>> Yup, per the CREATE TRIGGER documentation [1]:
>>
>> arguments
>>
>> An optional comma-separated list of arguments to be provided to the
>> function when the trigger is executed. The arguments are literal
>> string constants. Simple names and numeric constants can be written
>> here, too, but they will all be converted to strings. Please check
>> the
>> description of the implementation language of the trigger function to
>> find out how these arguments can be accessed within the function; it
>> might be different from normal function arguments.
>>
>>> I strongly feel this is a bug,
>>
>> It's operating as designed. There might be scope for a feature
>> improvement here, but it'd be a far-from-trivial task, with probably
>> a lot of ensuing compatibility breakage.
>>
>
> Oh, thanks! I did not read careful enough. I could not imagine such a
> behaviour to be intentional.
>
> Well, I guess, I can put the correct schema at installation, but would
> have liked to have a more general approach. Furthermore, I think this
> also implies that installation can only be done by psql. :-s
Why not grab the CURRENT_SCHEMA in the function?:
DECLARE
V_COUNT smallint;
C_SCHEMA varchar := CURRENT_SCHEMA;
>
--
Adrian Klaver
adrian.klaver@aklaver.com
Quoting Adrian Klaver <adrian.klaver@aklaver.com>: >> Well, I guess, I can put the correct schema at installation, but >> would have liked to have a more general approach. Furthermore, I >> think this also implies that installation can only be done by psql. >> :-s > > Why not grab the CURRENT_SCHEMA in the function?: > > DECLARE > V_COUNT smallint; > C_SCHEMA varchar := CURRENT_SCHEMA; Thank you all for your lightning fast replies. I failed to set it at install time. \gset cannot be used as bind variables in SQL. So, I try retaining the restriction to that the function needs to be installed in the same schema as the triggered table. -- 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
On 1/3/21 10:27 AM, Thiemo Kellner wrote: > > Quoting Adrian Klaver <adrian.klaver@aklaver.com>: > >>> Well, I guess, I can put the correct schema at installation, but >>> would have liked to have a more general approach. Furthermore, I >>> think this also implies that installation can only be done by psql. :-s >> >> Why not grab the CURRENT_SCHEMA in the function?: >> >> DECLARE >> V_COUNT smallint; >> C_SCHEMA varchar := CURRENT_SCHEMA; > > Thank you all for your lightning fast replies. I failed to set it at > install time. \gset cannot be used as bind variables in SQL. So, I try > retaining the restriction to that the function needs to be installed in > the same schema as the triggered table. > Alright you lost me. Can you provide an outline form of what you are trying to accomplish? -- Adrian Klaver adrian.klaver@aklaver.com
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 (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. 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. -- 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
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
Quoting Adrian Klaver <adrian.klaver@aklaver.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
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.klaver@aklaver.com
Quoting Adrian Klaver <adrian.klaver@aklaver.com>: > On 1/3/21 1:44 PM, Thiemo Kellner wrote: > 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? Yes insert only, I reckon there is no way to automatically handle deletes of functions - unless I could install a trigger on the very catalogue table which I will not consider even as last resort. I also discarded the update because I am only interested in the presence check. So, if my dimension table changes some payload attribute values, I do not need to propagate this change anywhere. On the other hand, if someone changes the value of DB_ROUTINE_NAME, I better check. It is a project of my own. There is no process defined. ;-) >> 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