From: | barrybrown(at)sierracollege(dot)edu |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #7543: Invalid table alias: DELETE FROM table * |
Date: | 2012-09-16 09:01:13 |
Message-ID: | E1TDAj3-0008JO-V0@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-docs |
The following bug has been logged on the website:
Bug reference: 7543
Logged by: Barry Brown
Email address: barrybrown(at)sierracollege(dot)edu
PostgreSQL version: 9.1.5
Operating system: Ubuntu 12.04
Description:
I sometime see my users delete all rows from a table using a command like
this:
DELETE FROM customer *;
The question is: what is the star? Is it a table alias or an
output_expression?
The grammar for DELETE is:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
According to the grammar, the star could be an alias. We can alias the table
being affected with a "normal" alias. The following also works:
DELETE FROM customer foo;
DELETE FROM customer foo where foo.id = 1;
However, the star isn't treated as an alias in this next command. Instead,
it generates a syntax error, as one might expected:
DELETE FROM customer * where *.id = 1;
So perhaps the star is an output_expression. The grammar for the RETURNING
clause is:
RETURNING * | output_expression [ [ AS ] output_name ] [, ...]
Since it has no parentheses to alter the operator precedence, it is
interpreted as:
(RETURNING *) | (output_expression [ [ AS ] output_name ] [, ...])
In other words, one can write just an output_expression without the
RETURNING keyword. However, I'm sure the grammar is supposed to require the
RETURNING keyword. Consequently, the grammar ought to be:
RETURNING (* | output_expression [ [ AS ] output_name ] [, ...])
The written documentation implies that * is a valid output_expression.
In summary:
Should * be an acceptable table alias for the DELETE command?
Should the grammar for the RETURNING clause include some parentheses to make
it clearer what's expected?
From | Date | Subject | |
---|---|---|---|
Next Message | sharkke.w | 2012-09-16 13:54:10 | BUG #7544: cmd, chcp 65001, show client_encoding, get "not enough memory" error |
Previous Message | Amit kapila | 2012-09-16 06:10:43 | Re: [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2012-09-16 21:39:34 | Re: [PERFORM] Planner selects different execution plans depending on limit |
Previous Message | Sebastien FLAESCH | 2012-09-14 09:43:43 | Re: Details about libpq cross-version compatibility |