BUG #1214: ecpg regression in 8.0.0beta1

The following bug has been logged online:

Bug reference: 1214
Logged by: R. Scott Bailey

Email address: scott(dot)bailey(at)eds(dot)com

PostgreSQL version: 7.5 Dev

Operating system: Tru64 UNIX V5.1B

Description: ecpg regression in 8.0.0beta1

Details:

The following code snippet does not compile under 8.0.0beta1:

--- extract from load-day.pgc ---

exec sql CREATE TEMPORARY TABLE OverallData (Class, Parameter, DataVal)
AS SELECT Class, Paramter, SUM(DataVal)
FROM PatrolData JOIN Metrics ON Parameter = MetricName
WHERE Aggregate = 1
GROUP BY Class, Parameter, RSBMIN(Tstamp) ;

--- end of extract ---

I get this:
# ecpg -t load-day.pgc
load-day.pgc:200: ERROR: syntax error at or near "SELECT"

Line 200 is the second line of the snippet above, if you couldn't guess
that. :-) This compiled cleanly under 7.4.3 and prior versions. For thrills,
I tried typing this command manually in psql and it worked fine. I'd be
happy to send you more source code and/or database metadata but it doesn't
feel to me like that is central this issue, since theoretically the ecpg
preprocessor has no real knowledge of the underlying database.

For what it may be worth, I have a number of programs I have been
recompiling and it appears to me that the common factor in what works and
what doesn't is that "CREATE TEMPORARY TABLE ... AS SELECT ..." seems to
fail and programs that do not contain this construct compile successfully.

Hope this helps,
Scott

"PostgreSQL Bugs List" <pgsql-bugs(at)postgresql(dot)org> writes:
> The following code snippet does not compile under 8.0.0beta1:

> exec sql CREATE TEMPORARY TABLE OverallData (Class, Parameter, DataVal)
> AS SELECT Class, Paramter, SUM(DataVal)
> FROM PatrolData JOIN Metrics ON Parameter = MetricName
> WHERE Aggregate = 1
> GROUP BY Class, Parameter, RSBMIN(Tstamp) ;

Looks like a slip-up in copying a change from the main grammar. I've
applied the attached patch to fix it. Thanks for the report!

regards, tom lane

*** src/interfaces/ecpg/preproc/preproc.y.orig Wed Jul 21 18:40:54 2004
--- src/interfaces/ecpg/preproc/preproc.y Wed Aug 11 18:30:40 2004
***************
*** 1546,1559 ****
* SELECT ... INTO.
*/

! CreateAsStmt: CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs AS
{ FoundInto = 0; }
SelectStmt
{
if (FoundInto == 1)
mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE / AS SELECT may not specify INTO");

! $$ = cat_str(8, make_str("create"), $2, make_str("table"), $4, $5, $6, make_str("as"), $9);
}
;

--- 1546,1559 ----
* SELECT ... INTO.
*/

! CreateAsStmt: CREATE OptTemp TABLE qualified_name OptCreateAs WithOidsAs
{ FoundInto = 0; }
SelectStmt
{
if (FoundInto == 1)
mmerror(PARSE_ERROR, ET_ERROR, "CREATE TABLE / AS SELECT may not specify INTO");

! $$ = cat_str(8, make_str("create"), $2, make_str("table"), $4, $5, $6, $8);
}
;

On Wed, Aug 11, 2004 at 06:51:59PM -0400, Tom Lane wrote:
> Looks like a slip-up in copying a change from the main grammar. I've
> applied the attached patch to fix it. Thanks for the report!

Thanks Tom.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!