Thread: Multiple operations when updating a view - works in Postgres, doesn't in the app

Hello,

I'm fairly new to the more advanded functionality of PostgreSQL,
especially writing functions in PL/pgSQL and have something of a
design question, which doesn't seem to be answered anywhere I can
google to.

I've a view created in my schema, for which I'm adding rules for
updating and inserting data. When data is updated or inserted into
that view, two tables in the database need to have inserted data into.

Unfortunately my first attempt of putting two INSERT statements into
the view rule's definition din't seem to be accepted by PostgreSQL.
So, my next idea was to put both these INSERTs into a function,
declared this way:

CREATE OR REPLACE FUNCTION create_trade_material(arg_diameter integer, arg_material_length real, arg_weight real,
arg_lossreal, arg_bar_type_id integer, arg_metal_type_id integer) 
  RETURNS void AS
$BODY$DECLARE
new_material_id INT;
BEGIN
  INSERT INTO material (diameter, material_length, weight, loss, bar_type_id, metal_type_id)
  VALUES (arg_diameter, arg_material_length, arg_weight, arg_loss, arg_bar_type_id, arg_metal_type_id)
  RETURNING "id" INTO new_material_id;

  INSERT INTO trade_material (material_id) VALUES (new_material_id);
  RETURN;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION create_trade_material(arg_diameter integer, arg_material_length real, arg_weight real, arg_loss real,
arg_bar_type_idinteger, arg_metal_type_id integer) OWNER TO hefajstos; 

and inside the view rule's definition I placed:

DO SELECT create_trade_material(new.diameter, new.material_length, new.weight, new.loss, new.bar_type_id,
new.metal_type_id)

It works as expected. Both INSERTing and UPDATE-ing the view does
modifications on both said tables, the return value though is an empty
record set (or actually a single null-tuple).


The problem is when I try to run INSERT or UPDATE with Hibernate in my
Java application. I get one of these ugly stack traces from the
exception:

  javax.transaction.RollbackException: Transaction marked for rollback.

and nowhere to see any explanation. Since the Java code is perfectly
correct, my guess is, that updating or inserting into the view the way
I did it doesn't return an appropriate value marking success, since
the function returns void and the INSERT or UPDATE returns that null
tuple.

What am I missing? Should I change the value returned by the create*
function? If yes, than two what? Or is calling the function through a
DO SELECT create* incorrect?


--
Best regards,
 Michal                          mailto:mpaluchowski@gmail.com