From: Antal Attila <atesz@ritek.hu>
To: pgsql-sql@postgresql.org
Subject: VIEWs and TEMP tables problem
Date: Thu, 16 Feb 2006 12:26:54 +0100
Message-ID: <43F4617E.9060708@ritek.hu> (raw)
Hi!
I found a problem with the views in PostgreSQL if I want to use
temporary tables in it. See the next case!
CREATE TABLE a(...., code INT4,...);
INSERT INTO a(...,code,...) VALUES (...,23,...);
CREATE TABLE actual_code(code INT4);
If I execute the next query, the result is empty.
SELECT * FROM actual_code;
CREATE VIEW a_view AS SELECT * FROM a JOIN actual_code AC ON (a.code =
AC.code);
CREATE TEMP TABLE actual_code AS SELECT 23::INT4 AS code;
If I execute the next query, the result contains exactly one row (code:
23). This is perfect.
SELECT * FROM actual_code;
After it if I compare the the next two queries, there will be
differences in the results.
1) SELECT * FROM a JOIN actual_code AC ON (a.code = AC.code); (Result
has one row!)
2) SELECT * FROM a_view; (Result is empty!)
In my opinion this queries should be equivalent with same results.
The problem is that the view use the original permanent table, but the
1) query use the temporary actual_code table.
I read the temporally table definition in the documentation
(http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html)
and I concluded it should be working.
Is it BUG, or FEATURE? Has anybody got good ideas for solve this problem?
This construction came to my mind, because I tried to solve my another
question:
http://groups.google.co.hu/group/pgsql.performance/browse_thread/thread/c7aec005f4a1f3eb/83fa0053cad...
Thanks your ideas!
Regards,
Antal Attila
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: atesz@ritek.hu
Subject: Re: VIEWs and TEMP tables problem
In-Reply-To: <43F4617E.9060708@ritek.hu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox