why semicolon after begin is not allowed in postgresql?

[AK]

I am reading the following in the documentation: "Tip: A common mistake is to
write a semicolon immediately after BEGIN. This is incorrect and will result
in a syntax error."

So, "common mistake" means semicolons after BEGIN seem consistent to many
people - it seems consistent to me as well. If PostgreSql allowed them, we
would have one less rule to memorize, shorter documentation, less mistakes
and so on. In other words, without this limitation PostgreSql would be
slightly more useful, right?

What am I missing? Why do we need this rule? How is it making PostgreSql
better?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Mike Blackwell]

I believe the section you are reading refers to the BEGIN keyword in the
procedural language plpgsql, not the SQL 'BEGIN' command. The issue stems
from confusing two distinct languages both of which, along with several
more procedural languages, are documented in the same manual.

__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
***@rrd.com
http://www.rrdonnelley.com


<http://www.rrdonnelley.com/>

Post by AK
I am reading the following in the documentation: "Tip: A common mistake is to
write a semicolon immediately after BEGIN. This is incorrect and will result
in a syntax error."
So, "common mistake" means semicolons after BEGIN seem consistent to many
people - it seems consistent to me as well. If PostgreSql allowed them, we
would have one less rule to memorize, shorter documentation, less mistakes
and so on. In other words, without this limitation PostgreSql would be
slightly more useful, right?
What am I missing? Why do we need this rule? How is it making PostgreSql
better?
--
http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
http://www.postgresql.org/mailpref/pgsql-hackers

[Merlin Moncure]

Post by Mike Blackwell
I believe the section you are reading refers to the BEGIN keyword in the
procedural language plpgsql, not the SQL 'BEGIN' command. The issue stems
from confusing two distinct languages both of which, along with several more
procedural languages, are documented in the same manual.

This is inherited constraint from Oracle pl/sql which pl/pgsql is, uh,
inspired by. In pl/sql, all block opening constructs (THEN, LOOP,
BEGIN) do not get semi-colons. BEGIN is a weird case because it's
(quite unfortunately) also the same thing that explicitly opens a
transaction in vanilla SQL; you use a semi-colon there as with any
standard SQL statement.

merlin

[Adrian Klaver]

Post by AK
I am reading the following in the documentation: "Tip: A common mistake is to
write a semicolon immediately after BEGIN. This is incorrect and will result
in a syntax error."
So, "common mistake" means semicolons after BEGIN seem consistent to many
people - it seems consistent to me as well. If PostgreSql allowed them, we
would have one less rule to memorize, shorter documentation, less mistakes
and so on. In other words, without this limitation PostgreSql would be
slightly more useful, right?

In Postgresql it is allowed:

test=> BEGIN ;
BEGIN

In plpgsql it is not, which is where you got the above documentation.
That is because SQL BEGIN != plpgsql BEGIN

Post by AK
What am I missing? Why do we need this rule? How is it making PostgreSql
better?
--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Kevin Grittner]

Post by AK
I am reading the following in the documentation: "Tip: A common
mistake is to write a semicolon immediately after BEGIN. This is
incorrect and will result in a syntax error."
So, "common mistake" means semicolons after BEGIN seem consistent
to many people - it seems consistent to me as well. If PostgreSql
allowed them, we would have one less rule to memorize, shorter
documentation, less mistakes and so on. In other words, without
this limitation PostgreSql would be slightly more useful, right?
What am I missing? Why do we need this rule? How is it making
PostgreSql better?

I think it only seems confusing because PostgreSQL also uses BEGIN
as a synonym for START TRANSACTION (and people tend to use the
shorter synonym to save keystrokes).  In plpgsql BEGIN is not a
command, it is part of declaring a code block.  Wouldn't these look
funny to you?:

IF x = 1 THEN;
  ...
END IF;

CASE;
  WHEN x = 1 THEN
    ...
  WHEN x = 2 THEN
    ...
  ELSE
    ...
END;

LOOP;
  ...
END LOOP;

etc.

Why should BEGIN be different from the above when it is not a
command, but part of declaring a code block?

In the nearest analog in the SQL standard, the BEGIN/END block is
called a compound statement, and like any other statement it is
ended by a semicolon; the standard does not allow a semicolon after
the BEGIN.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[AK]

Kevin,

I do see your logic now, but this thing is a common mistake - it means that
this seems counter-intuitive to some people. What would happen if we applied
Occam's razor and just removed this rule?

All existing code would continue to work as is, and we would have one less
rule to memorize. That would make PostgreSql a slightly better product,
right?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905p5780216.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Andrew Dunstan]

Post by AK
Kevin,
I do see your logic now, but this thing is a common mistake - it means that
this seems counter-intuitive to some people. What would happen if we applied
Occam's razor and just removed this rule?
All existing code would continue to work as is, and we would have one less
rule to memorize. That would make PostgreSql a slightly better product,
right?

It would make it a worse product, being inconsistent and stupid. The
rule is that you use semicolons to terminate statements. 'begin' on its
own is not a complete statement. Therefore it should not be followed by
a semicolon.

Several people have explained this basis of the rule. It's not
counter-intuitive to me or lots of other people.

cheers

andrew

[David Johnston]

Post by AK
Kevin,
I do see your logic now, but this thing is a common mistake - it means
that this seems counter-intuitive to some people. What would happen if we
applied Occam's razor and just removed this rule?
All existing code would continue to work as is, and we would have one less
rule to memorize. That would make PostgreSql a slightly better product,
right?

I'm somewhat on the fence for this but am leaning toward maintaining
status-quo. Mostly because of the analogy with "IF ... END IF;" versus the
SQL BEGIN; command which is a entirely separate construct.

I would maybe change the documentation so that instead of simply dictating a
rule we explain why the syntax is the way it is - like this thread is doing.
If they consciously omit the semi-colon hopefully they also understand that
what they are beginning is a code-block in plpgsql as opposed to an SQL
transaction.

That said, technical purity isn't always a good answer. I'd be inclined to
let someone passionate enough about the idea implement it an critique
instead of dis-allowing it outright; but in the end that is likely to result
in the same end.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905p5780222.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Kevin Grittner]

Post by AK
I do see your logic now, but this thing is a common mistake - it
means that this seems counter-intuitive to some people. What
would happen if we applied Occam's razor and just removed this
rule?
All existing code would continue to work as is, and we would have
one less rule to memorize. That would make PostgreSql a slightly
better product,
right?

Possibly; but what happens if we want to use plpgsql syntax for
stored procedures which allow the BEGIN command some day?  We would
have lost the ability to distinguish that command from the start of
a compound statement.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

[Mark Kirkwood]

Post by AK
Kevin,
I do see your logic now, but this thing is a common mistake - it means that
this seems counter-intuitive to some people. What would happen if we applied
Occam's razor and just removed this rule?
All existing code would continue to work as is, and we would have one less
rule to memorize. That would make PostgreSql a slightly better product,
right?

Perhaps not a good use of Mr Occam's razor. Postgres supports many
procedural languages (e.g plperl, plpython) and all these have different
grammar rules from SQL - and from each other. We can't (and shouldn't)
try altering them to be similar to SQL - it would defeat the purpose of
providing a procedural environment where the given language works as
advertised.

So in the case of plpgsql - it needs to follow the Ada grammar,
otherwise it would be useless.

The fact that different languages may have similar or the same keywords
with different grammar and punctuation rules is just a fact or life (I
trip over that often when switching from perl to python in the same day)!

Regards

Mark

[David Johnston]

Postgres supports many procedural languages (e.g plperl, plpython) and all
these have different
grammar rules from SQL - and from each other. We can't (and shouldn't)
try altering them to be similar to SQL - it would defeat the purpose of
providing a procedural environment where the given language works as
advertised.
So in the case of plpgsql - it needs to follow the Ada grammar,
otherwise it would be useless.

I do not follow the "useless" conclusion - what, present day, does Ada got
to do with it? And the request is to alter only plpgsql, not "all the other
languages". To the casual end-user plpgsql is an internal language under
our full control and installed by default in all new releases. Is it really
unreasonable to expect us to design in some level of coordination between it
and SQL?

Cross-compatibility is a valid reason though I'm guessing with all the
inherent differences between our standard PL and other database's PLs that
making this change would not be a materially noticeable additional
incompatibility.

I'll even accept language consistency and "not worth the effort of
special-casing" but mostly because the error is immediate and obvious, and
the "solution" is simple and readily learned.

A side observation: why does "DECLARE" not require a block-end keyword but
instead "BEGIN" acts as effectively both start and end? BEGIN, IF, FOR,
etc... all come in pairs but DECLARE does not.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905p5780245.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Andrew Dunstan]

Post by David Johnston
A side observation: why does "DECLARE" not require a block-end keyword but
instead "BEGIN" acts as effectively both start and end? BEGIN, IF, FOR,
etc... all come in pairs but DECLARE does not.

A complete block is:

[ DECLARE declarations ]
BEGIN statements
[ EXCEPTIONS handlers ]
END

The declare and exceptions parts are optional, as indicated. Does that
make it clearer?


cheers

andrew

[David Johnston]

Post by Andrew Dunstan

Post by David Johnston
A side observation: why does "DECLARE" not require a block-end keyword but
instead "BEGIN" acts as effectively both start and end? BEGIN, IF, FOR,
etc... all come in pairs but DECLARE does not.

[ DECLARE declarations ]
BEGIN statements
[ EXCEPTIONS handlers ]
END
The declare and exceptions parts are optional, as indicated. Does that
make it clearer?

Doh!

IF / THEN / ELSE / ENDIF .... (concept, not syntax)

That also does help to reinforce the point being made here...

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905p5780250.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Josh Berkus]

Post by David Johnston
Doh!
IF / THEN / ELSE / ENDIF .... (concept, not syntax)
That also does help to reinforce the point being made here...
David J.

What point?

PL/pgSQL has been in use for 14 years. During that entire time, it has
always used a block-based syntax where only the end of the block takes
semicolons; this applies not just to BEGIN ... END, but also to IF ...
END IF, LOOP ... END LOOP, etc. It's a feature of the language. One
might as well ask why Python doesn't take semicolons at the end of the
line, or why Perl requires them, or why Java has all those squiggly
brackets.

PostgreSQL is very accepting of new procedural languages; you could
always create your own, with whatever syntax you want.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

[David Johnston]

Post by Josh Berkus

Post by David Johnston
Doh!
IF / THEN / ELSE / ENDIF .... (concept, not syntax)
That also does help to reinforce the point being made here...
David J.

What point?

That the status-quo should be maintained.

David J.




--
View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905p5780425.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[Mark Kirkwood]

Post by David Johnston

Postgres supports many procedural languages (e.g plperl, plpython) and all
So in the case of plpgsql - it needs to follow the Ada grammar,
otherwise it would be useless.

I do not follow the "useless" conclusion - what, present day, does Ada got
to do with it? And the request is to alter only plpgsql, not "all the other
languages". To the casual end-user plpgsql is an internal language under
our full control and installed by default in all new releases. Is it really
unreasonable to expect us to design in some level of coordination between it
and SQL?
Cross-compatibility is a valid reason though I'm guessing with all the
inherent differences between our standard PL and other database's PLs that
making this change would not be a materially noticeable additional
incompatibility.

I guess I was thinking "useless as an example of a PL/SQL or Ada
compatible language", which I probably should have stated fully - sorry.
While we do add extra features to plpgsql we don't usually add
deliberately PL/SQL or Ada incompatible ones. Where we do, sometimes
might wish we had not (ISTR a discussion about PERFORM).

Other posters have pointed out that adding the semi colon to BEGIN
confuses its main reason for existence - indicating the start of a code
block, and would also confuse the casual reader about whether a code
block or transaction was starting. All in all a materially noticeable
incompatibility!

regards

Mark

[Andrew Dunstan]

Post by AK
I am reading the following in the documentation: "Tip: A common mistake is to
write a semicolon immediately after BEGIN. This is incorrect and will result
in a syntax error."
So, "common mistake" means semicolons after BEGIN seem consistent to many
people - it seems consistent to me as well. If PostgreSql allowed them, we
would have one less rule to memorize, shorter documentation, less mistakes
and so on. In other words, without this limitation PostgreSql would be
slightly more useful, right?
What am I missing? Why do we need this rule? How is it making PostgreSql
better?

You're referring specifically to plpgsql, not to Postgres or SQL generally.

plpgsql is derived from PLSQL which is derived from Ada which has this
grammatical rule.

The explanation is this: only complete statements are followed by
semicolons. But in these languages, "begin" on its own is not a complete
statement. It's the start of a compound statement, the end of which will
be "end", which is indeed followed by a semicolon.

cheers

andrew