From: | Mike Haberman <mikeh(at)ncsa(dot)uiuc(dot)edu> |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Subject: | PL/pgSQL help |
Date: | 1999-06-02 22:27:29 |
Message-ID: | 199906022227.RAA02362@pecos.ncsa.uiuc.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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';
From | Date | Subject | |
---|---|---|---|
Next Message | Ross J. Reedstrom | 1999-06-02 23:53:58 | Re: [GENERAL] create table bug: the "no" string can't be a column |
Previous Message | Marc Andre Paquin | 1999-06-02 18:11:18 | create table bug: the "no" string can't be a column |