PL/pgSQL help
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';
"MH" == Mike Haberman <mikeh@ncsa.uiuc.edu> writes:
MH> I'm a bit new to plpgsql, so this may be an easy question,
MH> I've got a function (see below) that inserts into 3 different
MH> tables. Each table has a SERIAL type for it's primary key.
MH> Question 1:
MH> I want to group all 3 inserts as a transacation.
MH> but when I put BEGIN WORK and COMMIT I get the error (at run
time):
Really this is compile time for your function: its text compiles when
it is first time called.
MH> NOTICE: plpgsql: ERROR during compile of easy_add near line 21
MH> ERROR: parse error at or near ""
MH> this is the line with COMMIT on it;
MH> What am i doing wrong?
MH> Also, do I also need to specify a ROLLBACK if any of the inserts
fail?
Any transaction operators, such as 'commit', 'rollback', etc not
allowed in 'plpgsql' functions. Only function _call_ as a unit can be
into transaction block.
MH> Question 2:
MH> is there a way to get the value of the newly assigned primary key
MH> after an insert? (rather then following the insert with a select)
MH> e.g. (this would be nice if it worked (networkID is the PKey))
MH> INSERT into Network (parentID, networkName) values (pid, mname);
MH> netid := new.networkID;
Yes. 'serial' type implements as 'int' type for field and sequence,
which mane is <tablename>_<fieldname>_seq. So you can do this:
INSERT into Network (parentID, networkName) values (pid, mname);
netid := Network_networkID_seq.last_value;
--
Anatoly K. Lasareff Email: tolik@icomm.ru
Senior programmer
Import Notes
Reply to msg id not found: MikeHaberman'smessageofWed2Jun1999172729-0500CDT
MH> Question 2:
MH> is there a way to get the value of the newly assigned primary key
MH> after an insert? (rather then following the insert with a select)MH> e.g. (this would be nice if it worked (networkID is the PKey))
MH> INSERT into Network (parentID, networkName) values (pid, mname);
MH> netid := new.networkID;Yes. 'serial' type implements as 'int' type for field and sequence,
which mane is <tablename>_<fieldname>_seq. So you can do this:INSERT into Network (parentID, networkName) values (pid, mname);
netid := Network_networkID_seq.last_value;
Just so I can clarify this, does it work "multi-user". eg if this was the sequence of events:
user 1:
INSERT into Network (parentID, networkName) values (pid1, mname1);
user 2:
INSERT into Network (parentID, networkName) values (pid2, mname2);
user 1:
netid1 := Network_networkID_seq.last_value;
would user 1 be given the correct id value? (assuming we maintain the connection).
btw, I'm using java for my midleware
cheers
timj
tim@hoop.co.uk
Import Notes
Resolved by subject fallback
On 3 Jun 1999, Anatoly K. Lasareff wrote:
# Yes. 'serial' type implements as 'int' type for field and sequence,
# which mane is <tablename>_<fieldname>_seq. So you can do this:
#
# INSERT into Network (parentID, networkName) values (pid, mname); netid
# := Network_networkID_seq.last_value;
That doesn't tell you the last value you added, that tells you the
last value that was added at all. currval('network_networkid_seq') tells
you the last one you added.
--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________
At 22:08 +0300 on 03/06/1999, Dustin Sallings wrote:
That doesn't tell you the last value you added, that tells you the
last value that was added at all. currval('network_networkid_seq') tells
you the last one you added.
Plus currval is multiuser...
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma