From: Richard Huxton <dev@archonet.com>
To: Antal Attila <atesz@ritek.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: VIEWs and TEMP tables problem
Date: Thu, 16 Feb 2006 11:57:48 +0000
Message-ID: <43F468BC.2020207@archonet.com> (raw)
In-Reply-To: <43F4617E.9060708@ritek.hu>
References: <43F4617E.9060708@ritek.hu>
Antal Attila wrote:
> Hi!
>
> I found a problem with the views in PostgreSQL if I want to use
> temporary tables in it. See the next case!
[snip]
> 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?
Feature, I'd guess.
When the view was built there was no TEMP TABLE. If you do \d a_view
you'll see it actually will link to something like "public.a". If it
didn't, your view could change depending on your search_path settings
and even break.
You'll see a similar problem with plpgsql functions.
> 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...
I think you need to explain why you're trying to do this:
CREATE VIEW ab_view AS
SELECT a.id AS id,
a.userid AS userid_a, b.userid AS userid_b,
a.col AS col_a, b.col AS col_b
FROM a LEFT JOIN b ON (a.id = b.a_id);
EXPLAIN ANALYSE SELECT * FROM ab_view
WHERE userid_a = 23 AND userid_b = 23 AND col_a LIKE 's%'
ORDER BY col_b
LIMIT 10 OFFSET 10;
If you want userid_a=userid_b ALWAYS to be the same, just put it in the
view. If you sometimes want them different, then you'll have to provide
two parameters anyway. It's not clear how you intend to use this.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, atesz@ritek.hu
Subject: Re: VIEWs and TEMP tables problem
In-Reply-To: <43F468BC.2020207@archonet.com>
* 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