Insert with query

[Juliano Amaral Chaves <juliano.amaral@xxxxxxxxxxx>]

By doing insert into a table using a query, it seems that all records of consultation were included bypassing the AFTER INSERT triggers and as few as after all the records already included, the TRIGGER is fired for each record, I wonder if this occurrence is normal.

I created an example that demonstrates my doubt, where the 1st case works properly. The 2nd case is restricted by TRIGGER when I think, that could not be

---- EXEMPLE ----

drop table if exists test;

create table test(chave serial primary key, campo1 integer, campo2 integer, campo3 integer);

CREATE OR REPLACE FUNCTION ft_block()

  RETURNS trigger AS

$BODY$

declare 

 vMax integer;

begin

  select max(coalesce(campo3, campo2)) from test where campo1 = new.campo1 into vMax;

  if vMax > coalesce(new.campo3, new.campo2) then

   raise 'Erro na inclusão: %, %, %, %', new.campo1, new.campo2, new.campo3, vMax;

  end if;

return null;

end;$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

CREATE TRIGGER t_block

  AFTER INSERT 

  ON test

  FOR EACH ROW

  EXECUTE PROCEDURE ft_block();

create or replace function f_insert_in_test()

returns boolean as

$BODY$

declare

  r record;

begin

  for r in (select * from (values (1, 1, null), (1, 2, 3), (1, 4, null), (1, 5, null)) as x) loop    

    insert into test(campo1, campo2, campo3) values(r.column1, r.column2, r.column3);

  end loop;

  return true;

end;

$BODY$

language plpgsql;

--- 1º CASE -- OK

delete from test;

select f_insert_in_test();

--- 2º CASE --- BLOCK

delete from test;

insert into test(campo1, campo2, campo3) (select * from (values (1, 1, null), (1, 2, 3), (1, 4, null), (1, 5, null)) as x);

select * from test;