Thread: PL/PGSQL question on transactions
Can you imbed BEGIN; and COMMIT; statements in the body of a pl/pgsql function? I am getting a parse error upon execution when I try it. Code: CREATE FUNCTION chitadj(integer,integer) RETURNS integer AS ' DECLARE result integer; oldamount integer; BEGIN BEGIN; SELECT INTO oldamount chits FROM avatar WHERE avatar_id = $1; IF oldamount IS NULL THEN RETURN -1; END IF; IF ((oldamount < abs($2)) AND ($2 < 0)) THEN RETURN -2; END IF; result := oldamount + $2; RETURN result; COMMIT; END; ' language 'plpgsql'; ------------------------- Dan _________________________________________________________________ Join the world�s largest e-mail service with MSN Hotmail. http://www.hotmail.com
No - a function implicity runs in a transaction anyway. Chris On Fri, 12 Apr 2002, Dan Schwitalla wrote: > Can you imbed BEGIN; and COMMIT; statements in the body of a pl/pgsql > function? > > I am getting a parse error upon execution when I try it. > > Code: > > CREATE FUNCTION chitadj(integer,integer) RETURNS integer AS ' > DECLARE > result integer; > oldamount integer; > BEGIN > BEGIN; > SELECT INTO oldamount chits FROM avatar > WHERE avatar_id = $1; > > IF oldamount IS NULL THEN > RETURN -1; > END IF; > > IF ((oldamount < abs($2)) AND ($2 < 0)) THEN > RETURN -2; > END IF; > > result := oldamount + $2; > RETURN result; > COMMIT; > END; > ' language 'plpgsql'; > > > > ------------------------- > Dan > > _________________________________________________________________ > Join the world�s largest e-mail service with MSN Hotmail. > http://www.hotmail.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Nope. As my understanding, in a PL/pgSQL function, all statements are in ONE transaction, you do not need another pair of begin/end. If transaction failed, it abort whole thing anyway. Jie Liang -----Original Message----- From: Dan Schwitalla [mailto:acadrace@hotmail.com] Sent: Friday, April 12, 2002 8:50 AM To: pgsql-sql@postgresql.org Subject: [SQL] PL/PGSQL question on transactions Can you imbed BEGIN; and COMMIT; statements in the body of a pl/pgsql function? I am getting a parse error upon execution when I try it. Code: CREATE FUNCTION chitadj(integer,integer) RETURNS integer AS ' DECLARE result integer; oldamount integer; BEGIN BEGIN; SELECT INTO oldamount chits FROM avatar WHERE avatar_id = $1; IF oldamount IS NULL THEN RETURN -1; END IF; IF ((oldamount < abs($2)) AND ($2 < 0)) THEN RETURN -2; END IF; result := oldamount + $2; RETURN result; COMMIT; END; ' language 'plpgsql'; ------------------------- Dan _________________________________________________________________ Join the world's largest e-mail service with MSN Hotmail. http://www.hotmail.com ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org