The following bug has been logged online:

Bug reference: 2440
Logged by: Peter Exner
Email address: exner@his.de
PostgreSQL version: 8.0.3 / 8.1.3
Operating system: SuSe Linux
Description: TEMP TABLES in Postgres 8.1.3
Details:

Hello,

there is a problem with my "tmp_table". My C++ program creates it with
"SELECT ... INTO tmp_table", then uses it and finally drops it. With
Postgres 7.x and Postgres 8.0.3 everything works fine.
Now I switched to Postgres 8.1.3, and an error occurs, as shown below.

Is it a bug, or what can I do?

Thanks for answers!
Peter


correct (with Postgres 8.0.3):

1. create "tmp_table"

SQL-Statement:
SELECT MAX(table1_id) AS max_id, SUM(value1) AS value1_sum INTO tmp_table
FROM table1 GROUP BY value2;

2. do something with "tmp_table"

SQL-Statement:
UPDATE table1 SET value1 = value1 - (SELECT value1_sum FROM tmp_table WHERE
table1.table1_id = tmp_table.max_id) WHERE split_anteil < 10000 AND afa_ende
= 24061 AND table1.table1_id = tmp_table.max_id;

3. drop "tmp_table"

SQL-Statement:
DROP TABLE tmp_table;

everything o.k.!


false (with Postgres 8.1.3), same SQL-statements as above

1. create "tmp_table"

SQL-Statement:
SELECT MAX(table1_id) AS max_id, SUM(value1) AS value1_sum INTO tmp_table
FROM table1 GROUP BY value2;

2. do something with "tmp_table"

SQL-Statement:
UPDATE table1 SET value1 = value1 - (SELECT value1_sum FROM tmp_table WHERE
table1.table1_id = tmp_table.max_id) WHERE split_anteil < 10000 AND afa_ende
= 24061 AND table1.table1_id = tmp_table.max_id;

doesn't work:

ODBC-Error - SQLExecDirect/ExecuteSQL:
SQLSTATE = S1000
Native Error = 7
Error Message: FEHLER: fehlender Eintrag in FROM-Klausel für Tabelle
»tmp_table«



3. drop "tmp_table"

SQL-Statement:
DROP TABLE tmp_table;

doesn't work:

ODBC-Error - SQLExecDirect/ExecuteSQL:
SQLSTATE = S1000
Native Error = 7
Error Message: FEHLER: Tabelle »tmp_table« existiert nicht

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Peter Exner" <exner@his.de> writes:
> SQL-Statement:
> UPDATE table1 SET value1 = value1 - (SELECT value1_sum FROM tmp_table WHERE
> table1.table1_id = tmp_table.max_id) WHERE split_anteil < 10000 AND afa_ende
> = 24061 AND table1.table1_id = tmp_table.max_id;

That's not standard SQL. You can fix it to work under 8.1 the same as
it did before by adding "FROM tmp_table", or if you are stubborn you can
turn add_missing_from on ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly