Thread: 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

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