Insert with query

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;

Responses

Browse pgsql-general by date

  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