From: | Juliano Amaral Chaves <juliano(dot)amaral(at)hotmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Insert with query |
Date: | 2013-05-24 19:26:21 |
Message-ID: | BAY173-W145957AC26F1F4FF9ED95A89AB0@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 -- OKdelete from test;select f_insert_in_test();
--- 2º CASE --- BLOCKdelete 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;
From | Date | Subject | |
---|---|---|---|
Next Message | PG User | 2013-05-24 20:16:53 | Re: data file corruption |
Previous Message | Karl Denninger | 2013-05-24 15:55:44 | Re: Success stories of PostgreSQL implementations in different companies |