From: | tolik(at)icomm(dot)ru (Anatoly K(dot) Lasareff) |
---|---|
To: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: [GENERAL] PL/pgSQL help |
Date: | 1999-06-03 11:41:38 |
Message-ID: | 876755mryl.fsf@tolikus.hq.aaanet.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "MH" == Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)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(at)icomm(dot)ru
Senior programmer
From | Date | Subject | |
---|---|---|---|
Next Message | The Hermit Hacker | 1999-06-03 11:53:51 | Re: [GENERAL] Parallelizing PostgreSQL for Cluster |
Previous Message | taipan | 1999-06-03 09:50:26 | reatedb: database creation failed on mydb |