473,883 Members | 1,582 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trouble EXITing plpgsql labeled BEGIN blocks with DECLAREs

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
1 2215
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
33617
by: Thomas LeBlanc | last post by:
I copied an example from the help: CREATE FUNCTION somefunc() RETURNS integer AS ' DECLARE quantity integer := 30; BEGIN RAISE NOTICE ''Quantity here is %'', quantity; -- Quantity here is 30 quantity := 50; -- -- Create a subblock
5
9408
by: Oksana Yasynska | last post by:
Hi all, I'm running Postgres 7.2.1 and I need to return multiple row sets from plpgsql function. I'm new in plpgsql but according documentation and everything I could find in the mailing list I need to switch to 7.3 to get at least SETOF rows as a result. I can't really upgrade Postgres now. Is there is any a workaround idea to retrieve multiple rowsets?
6
3986
by: Martin Marques | last post by:
We are trying to make some things work with plpgsql. The problem is that I built several functions that call one another, and I thought that the way of calling it was just making the assign: var:=func1(arg1,arg2); which gave me an error near ")". Now if I did the same, but like this:
10
2953
by: lnd | last post by:
After copied pg database from one PC to another -I could not find plpgsql function(s) in the copied database. -had to instal plpgsql language handler again -whilst tables and data moved fine The copy included all under /cygwin/usr/local/pgsql/data and database was down while making a copy.
5
1613
by: Thomas Chille | last post by:
Hi, i am playing around with PLpgSQL and can not solve one problem: I am fetching some rows of a special rowtype and wanna give this rows step by step to a function with this rowtype as parameter. evertime i get the errormessage: ERROR: Attribut »_row« not found but the Attribut exists and has values in his fields. i have absolutly no idea was happens there. can someone help me please? thanx
0
1203
by: Shanmugasundaram Doraisamy | last post by:
Dear Group, We are using Postgresql 7.3.4 on Redhat 8.0 with Java 1.4.2. We are developing our applications in Java. We call stored procedures from the java program. Order numbers are generated by many departments in the Hospital. We manitain a single table from which to select the order number. The way this works is that the order numbers are released for reuse if the order has been completed. We wrote a procedure in plpgsql with a...
0
1122
by: Google Mike | last post by:
I had RH9 Linux. It came with pgSQL, but I couldn't seem to figure out how to get PL/pgSQL going. I read the HTML documentation that came with it and was confused until I tried a few different variations and guessed about some things. I've finally got it working and here's what the SQL looks like to enable and test it: -- ON YOUR SYSTEM, LOOK FOR WHERE plpgsql.so FILE EXISTS AND SUBSTITUTE THAT -- PATH WITH THE ONE I HAVE BELOW.
9
10942
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make that work either. Anyway, what's wrong with this?) Version is: $ rpm -q postgresql
5
13401
matheussousuke
by: matheussousuke | last post by:
Hello, I'm using tiny MCE plugin on my oscommerce and it is inserting my website URL when I use insert image function in the emails. The goal is: Make it send the email with the URL http://mghospedagem.com/images/controlpanel.jpg instead of http://mghospedagem.comhttp://mghospedagem.com/images/controlpanel.jpg As u see, there's the website URL before the image URL.
0
9779
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11114
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10730
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10835
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5787
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4605
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.