Обсуждение: Me again with an insert trigger problem

Hi

I am surprised that my before insert trigger function does not insert 
any rows into NODE_GOOD.

I was under the impression that the trigger function would do the insert 
with the new and possibly adapted values. In my case, to me at least, it 
is very simple. Only records of node type "Drop-off" must and are 
allowed to have a task name. If this is not given, raise an exception. 
What am I missing?

Function code:
     create or replace function NODE_GOOD⠒TR_B_IU_R()
       returns trigger
       language plpgsql
       stable
       set search_path = SNOWRUNNER,
                         PUBLIC
       as
     $body$
         declare
             V⠒NODE_TYPE⠒NAME                NODE⠒V.NODE_TYPE⠒NAME%type 
:= null;
         begin
             -- raise info ': %', ;
             raise info 'new.NODE⠒ID: %', new.NODE⠒ID;
             raise info 'new.TASK_NAME: %', new.TASK_NAME;
             select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME
               from NODE⠒V
              where 1 = 1
                and ID = new.NODE⠒ID
                and 1 = 1;
             raise info 'V⠒NODE_TYPE⠒NAME: %', V⠒NODE_TYPE⠒NAME;
             if (    V⠒NODE_TYPE⠒NAME = 'Drop-off'
                 and new.TASK_NAME is null) then
                 raise exception 'A good connection to a drop-off node 
must have a task name!';
             elsif (    V⠒NODE_TYPE⠒NAME != 'Drop-off'
                    and new.TASK_NAME is not null) then
                 raise exception 'A good connection to a non-drop-off 
node cannot have a task name!';
             end if;
             raise info 'Going to leave the trigger function 
"NODE_GOOD⠒TR_B_IU_R"';
             return null;
         end;
     $body$;


The output of the important part of the install script is listed at the end.

Please find attached my code.

Kind regards

Thiemo


insert data into NODE_GOOD⠒V
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
1107cb8d-c1f1-4368-ac7b-72ac3031555a
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: Landslide 
on the Highway
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Drop-off
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
cdb25b50-e6cf-46fe-85f6-47ec72c00a22
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
59dec625-9167-4e63-9022-917e1a751206
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
b4fd810a-2065-4bcc-bd1d-49021d7ade95
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
a3459f1d-2615-4b20-946b-daca4a9e69de
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
c0069eea-0ee0-44ca-8b15-c14e59230a75
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
5917e5d2-bc16-4126-8486-6a8bedca45aa
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
86abd010-d930-4486-9a5e-1e85d8e81faa
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
b80adef3-8233-4e20-8f8e-3a5ccf04aacd
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
f51c6a96-ffbb-433b-8402-2b4dc467b689
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
37e29f40-9da0-44e7-a601-06dfa94043e6
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 
e8d2c14d-37bd-4c11-a3c6-55cd382fd414
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the 
trigger function  "NODE_GOOD⠒TR_B_IU_R"
INSERT 0 0
COMMIT

Hi

I am surprised that my before insert trigger function does not insert any rows into NODE_GOOD.

I was under the impression that the trigger function would do the insert with the new and possibly adapted values. In my case, to me at least, it is very simple. Only records of node type "Drop-off" must and are allowed to have a task name. If this is not given, raise an exception. What am I missing?

Function code:
    create or replace function NODE_GOOD⠒TR_B_IU_R()
      returns trigger
      language plpgsql
      stable
      set search_path = SNOWRUNNER,
                        PUBLIC
      as
    $body$
        declare
            V⠒NODE_TYPE⠒NAME                NODE⠒V.NODE_TYPE⠒NAME%type := null;
        begin
            -- raise info ': %', ;
            raise info 'new.NODE⠒ID: %', new.NODE⠒ID;
            raise info 'new.TASK_NAME: %', new.TASK_NAME;
            select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME
              from NODE⠒V
             where 1 = 1
               and ID = new.NODE⠒ID
               and 1 = 1;
            raise info 'V⠒NODE_TYPE⠒NAME: %', V⠒NODE_TYPE⠒NAME;
            if (    V⠒NODE_TYPE⠒NAME = 'Drop-off'
                and new.TASK_NAME is null) then
                raise exception 'A good connection to a drop-off node must have a task name!';
            elsif (    V⠒NODE_TYPE⠒NAME != 'Drop-off'
                   and new.TASK_NAME is not null) then
                raise exception 'A good connection to a non-drop-off node cannot have a task name!';
            end if;
            raise info 'Going to leave the trigger function "NODE_GOOD⠒TR_B_IU_R"';
            return null;

Assuming this is row level trigger and run BEFORE:

https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER

"

Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value.

"

        end;
    $body$;


The output of the important part of the install script is listed at the end.

Please find attached my code.

Kind regards

Thiemo


insert data into NODE_GOOD⠒V
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 1107cb8d-c1f1-4368-ac7b-72ac3031555a
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: Landslide on the Highway
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Drop-off
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: cdb25b50-e6cf-46fe-85f6-47ec72c00a22
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 59dec625-9167-4e63-9022-917e1a751206
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: b4fd810a-2065-4bcc-bd1d-49021d7ade95
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: a3459f1d-2615-4b20-946b-daca4a9e69de
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: c0069eea-0ee0-44ca-8b15-c14e59230a75
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 5917e5d2-bc16-4126-8486-6a8bedca45aa
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 86abd010-d930-4486-9a5e-1e85d8e81faa
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: b80adef3-8233-4e20-8f8e-3a5ccf04aacd
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: f51c6a96-ffbb-433b-8402-2b4dc467b689
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: 37e29f40-9da0-44e7-a601-06dfa94043e6
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.NODE⠒ID: e8d2c14d-37bd-4c11-a3c6-55cd382fd414
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  new.TASK_NAME: <NULL>
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  V⠒NODE_TYPE⠒NAME: Pick-up
psql:insert_data/NODE_GOOD⠒V.pg_sql:27: INFO:  Going to leave the trigger function  "NODE_GOOD⠒TR_B_IU_R"
INSERT 0 0
COMMIT

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Am 27.02.2024 um 21:42 schrieb Adrian Klaver:
> Also not sure what this:
> 
> select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME
>                from NODE⠒V
>               where 1 = 1
>                 and ID = new.NODE⠒ID
>                 and 1 = 1;
> 
> is supposed to be doing especially the 1 = 1 tests?

The select retrieves the type of the node in order to determine whether 
a task name must be given or not.

It is a habit of mine to pad conditions in the where clause. This way, 
it is easy to comment/uncomment parts of the clause for testing 
purposes. Coming from Oracle, I missed that using "true" is also 
possible and better because clearer.

> Seems  '... where  ID = new.NODE⠒ID ...' is sufficient.

Right. Semantically sufficient.


> Also what do you want to return as NEW?

The unchanged new record. And it works as intended.

Thanks for your help!

On 2/27/24 14:11, Thiemo Kellner wrote:
> Am 27.02.2024 um 21:42 schrieb Adrian Klaver:
>> Also not sure what this:
>>
>> select NODE_TYPE⠒NAME into V⠒NODE_TYPE⠒NAME
>>                from NODE⠒V
>>               where 1 = 1
>>                 and ID = new.NODE⠒ID
>>                 and 1 = 1;
>>
>> is supposed to be doing especially the 1 = 1 tests?
> 
> The select retrieves the type of the node in order to determine whether 
> a task name must be given or not.
> 
> It is a habit of mine to pad conditions in the where clause. This way, 
> it is easy to comment/uncomment parts of the clause for testing 
> purposes. Coming from Oracle, I missed that using "true" is also 
> possible and better because clearer.


create table true_test(id integer);
insert into true_test select * from generate_series(1, 10000);

select count(*) from true_test where true;
  count
-------
  10000
(1 row)

select count(*) from true_test where id < 100 and true;
  count
-------
     99


> 
>> Seems  '... where  ID = new.NODE⠒ID ...' is sufficient.
> 
> Right. Semantically sufficient.
> 
> 
>> Also what do you want to return as NEW?
> 
> The unchanged new record. And it works as intended.
> 
> Thanks for your help!
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Am 27.02.2024 um 23:20 schrieb Adrian Klaver:
> On 2/27/24 14:11, Thiemo Kellner wrote:
>> It is a habit of mine to pad conditions in the where clause. This way, 
>> it is easy to comment/uncomment parts of the clause for testing 
>> purposes. Coming from Oracle, I missed that using "true" is also 
>> possible and better because clearer.
> 
> 
> create table true_test(id integer);
> insert into true_test select * from generate_series(1, 10000);
> 
> select count(*) from true_test where true;
>   count
> -------
>   10000
> (1 row)
> 
> select count(*) from true_test where id < 100 and true;
>   count
> -------
>      99

I am not sure, what you want me to show with your test case. And I am 
not sure whether I could not make myself clear. Please bear with me if I 
try to make things clearer with an example.

-- bit-harder-to-test-statement
select count(*)
   from TRUE_TEST
  where ID < 100  -- if I want to deactivate that part of the clause, I 
have to rewrite
    and mod(ID, 5) = 0;

-- bit-easier-to-test-statement
select count(*)
   from TRUE_TEST
  where true
    and ID < 100  -- if I want to deactivate that part of the clause, I 
just comment it out
    and mod(ID, 5) = 0
    and true;

Cheers

On 2/27/24 14:38, Thiemo Kellner wrote:
> 
> Am 27.02.2024 um 23:20 schrieb Adrian Klaver:

> 
> I am not sure, what you want me to show with your test case. And I am 
> not sure whether I could not make myself clear. Please bear with me if I 
> try to make things clearer with an example.

Your comment was:

"Coming from Oracle, I missed that using "true" is also possible and 
better because clearer."

Just showing true is available in Postgres also.

> 
> -- bit-harder-to-test-statement
> select count(*)
>    from TRUE_TEST
>   where ID < 100  -- if I want to deactivate that part of the clause, I 
> have to rewrite
>     and mod(ID, 5) = 0;
> 
> -- bit-easier-to-test-statement
> select count(*)
>    from TRUE_TEST
>   where true
>     and ID < 100  -- if I want to deactivate that part of the clause, I 
> just comment it out
>     and mod(ID, 5) = 0
>     and true;
> 
> Cheers
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com