By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,480 Members | 1,144 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,480 IT Pros & Developers. It's quick & easy.

Trouble EXITing plpgsql labeled BEGIN blocks with DECLAREs

P: n/a
FYI, mostly. But I do have questions as to how to write code
that will continue to work in subsequent postgresql versions.

See code below.

begintest() uses EXIT to exit a BEGIN block from within nested
loops. No problem.

begintest2() simplifies this, omitting the nested loops.
Still no problem.

begintest3() changes the return value of begintest2(), instead
of returning INT returns VOID. No problems.

begintest4() moves the declariation from the outermost BEGIN block
to the inner BEGIN block, the one that's EXITed. It fails with:

WARNING: plpgsql: ERROR during compile of begintest4 near line 9
ERROR: syntax error at or near "some_label"

I've written some code like begintest4(), if I change it to
begintest3() will it work forever? (Actually my code is a trigger
function and returns TRIGGER instead of VOID.)

It seems that having a DECLARE on a labeled BEGIN block is
the problem. ? Hence begintest5() which fails, and begintest6()
and 7 which works. The difference between 5 and 6 is
whether or not the inner BLOCK, the EXITed one,
DECLAREs a variable. The difference between 5 and 7 is
that 7 has another layer of BEGIN between <<label>>
and DECLARE, so again, whether the EXITed block has
a DECLARE.

I want to write the code with EXIT, intead of using RETURN,
so that the reader does not have to look through the code
to find RETURNs sprinkeled within. If he ever wants to add
code to be run just before the function exits he can just add
such code before the RETURN at the bottom of the function, without
having to refactor the routine's control structure. Can I do
this just be adding another layer of BEGIN block between
DECLARE and <<label>> (which fixed my code) or do I have to
give up and use RETURNS?

(This has the feel of an optimizer problem.)
=> select version();
PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC
i386-redhat-linux-gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
CREATE FUNCTION begintest()
RETURNS INT
LANGUAGE plpgsql
AS '
DECLARE
var INT;

BEGIN

<<somelabel>>
BEGIN

FOR i IN 1 .. 10 LOOP
var := i;
FOR j in 1 .. 10 LOOP
IF i >= 5 THEN
EXIT somelabel;
END IF;
END LOOP;
END LOOP;
END;

RETURN var;
END;
';

SELECT begintest();

DROP FUNCTION begintest();

CREATE FUNCTION begintest2()
RETURNS INT
LANGUAGE plpgsql
AS '
DECLARE
var INT;
BEGIN

<<some_label>>
BEGIN

var := 5;
EXIT some_label;

var := 0;
END;

RETURN var;
END;
';

SELECT begintest2();

DROP FUNCTION begintest2();

CREATE FUNCTION begintest3()
RETURNS VOID
LANGUAGE plpgsql
AS '
DECLARE
var INT;
BEGIN

<<somelabel>>
BEGIN

var := 5;
EXIT somelabel;

var := 0;
END;

RETURN NULL;
END;
';

SELECT begintest3();

DROP FUNCTION begintest3();
CREATE FUNCTION begintest4()
RETURNS VOID
LANGUAGE plpgsql
AS '
BEGIN
DECLARE
var INT;

<<some_label>>
BEGIN

var := 5;
EXIT some_label;

var := 0;
END;

RETURN NULL;
END;
';

SELECT begintest4();

DROP FUNCTION begintest4();
CREATE FUNCTION begintest5()
RETURNS INT
LANGUAGE plpgsql
AS '
DECLARE
othervar INT;

BEGIN
DECLARE
var INT;

<<some_label>>
BEGIN

var := 5;
EXIT some_label;

var := 0;
END;

othervar := 2;

RETURN othervar;
END;
';

SELECT begintest5();

DROP FUNCTION begintest5();
CREATE FUNCTION begintest6()
RETURNS INT
LANGUAGE plpgsql
AS '
DECLARE
othervar INT;
var INT;

BEGIN

<<some_label>>
BEGIN

var := 5;
EXIT some_label;

var := 0;
END;

othervar := 2;

RETURN othervar;
END;
';

SELECT begintest6();

DROP FUNCTION begintest6();

CREATE FUNCTION begintest7()
RETURNS INT
LANGUAGE plpgsql
AS '
DECLARE
othervar INT;

BEGIN
DECLARE
var INT;

BEGIN
<<some_label>>
BEGIN

var := 5;
EXIT some_label;

var := 0;
END;
END;

othervar := 2;

RETURN othervar;
END;
';

SELECT begintest7();

DROP FUNCTION begintest7();


Karl <ko*@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Karl O. Pinc" <ko*@meme.com> writes:
begintest4() moves the declariation from the outermost BEGIN block
to the inner BEGIN block, the one that's EXITed. It fails with: WARNING: plpgsql: ERROR during compile of begintest4 near line 9
ERROR: syntax error at or near "some_label"
You're misreading the syntax. A <<label>> applies to a whole block
including any declarations. So instead of
BEGIN
DECLARE
var INT;
<<some_label>>
BEGIN
var := 5;
EXIT some_label;
var := 0;
END;
RETURN NULL;
END;
write
BEGIN
<<some_label>>
DECLARE
var INT;
BEGIN
var := 5;
EXIT some_label;
var := 0;
END;
RETURN NULL;
END;


See the definition of "block" at the very top of
http://developer.postgresql.org/docs...structure.html
....

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.