Thread: why semicolon after begin is not allowed in postgresql?

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.

On 11/22/2013 02:24 PM, AK wrote:
> 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


>
> 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.
>
>


-- 
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Nov 22, 2013 at 4:34 PM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
> 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

AK <alkuzo@gmail.com> wrote:

> 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

On 11/22/2013 05:24 PM, AK wrote:
> 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

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.

On 11/25/2013 03:42 PM, AK wrote:
> 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

AK wrote
> 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.

AK <alkuzo@gmail.com> wrote:

> 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

On 26/11/13 09:42, AK wrote:
> 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

Mark Kirkwood-2 wrote
> 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.

On 11/25/2013 06:13 PM, David Johnston wrote:


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

Andrew Dunstan wrote
> On 11/25/2013 06:13 PM, David Johnston wrote:
>>
>> 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?

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.

On 26/11/13 12:13, David Johnston wrote:
> Mark Kirkwood-2 wrote
>> 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

On 11/25/2013 03:36 PM, David Johnston wrote:
> 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

Josh Berkus wrote
> On 11/25/2013 03:36 PM, David Johnston wrote:
>> 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.