PL/pgSQL help

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';

Responses

Browse pgsql-general by date

  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