PL/pgSQL help
От | Mike Haberman |
---|---|
Тема | PL/pgSQL help |
Дата | |
Msg-id | 199906022227.RAA02362@pecos.ncsa.uiuc.edu обсуждение исходный текст |
Список | pgsql-general |
I'm a bit new to plpgsql, so this may be an easy question, I've got a function (see below) that inserts into 3 different tables. Each table has a SERIAL type for it's primary key. Question 1: I want to group all 3 inserts as a transacation. but when I put BEGIN WORK and COMMIT I get the error (at run time): NOTICE: plpgsql: ERROR during compile of easy_add near line 21 ERROR: parse error at or near "" this is the line with COMMIT on it; What am i doing wrong? Also, do I also need to specify a ROLLBACK if any of the inserts fail? Question 2: is there a way to get the value of the newly assigned primary key after an insert? (rather then following the insert with a select) e.g. (this would be nice if it worked (networkID is the PKey)) INSERT into Network (parentID, networkName) values (pid, mname); netid := new.networkID; thanks for your time!!! DROP FUNCTION easy_add(int4, text, inet); CREATE FUNCTION easy_add(int4, text, inet) RETURNS int4 AS ' DECLARE pid alias for $1; mname alias for $2; ip alias for $3; netid int4; ipid int4; rec record; BEGIN -- BEGIN WORK; INSERT into Network (parentID, networkName) values (pid, mname); SELECT into rec * FROM Network WHERE networkName = mname; netid := rec.networkID; INSERT into AddressSpace (networkID, address) values (netid, ip); SELECT into rec * FROM AddressSpace WHERE networkID = netid AND address = ip; ipid := rec.addressID; INSERT into NetworkAddress(networkID, addressID) values (netid, ipid); -- COMMIT WORK; return 1; END; ' LANGUAGE 'plpgsql';
В списке pgsql-general по дате отправления: