Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 7FFBA1845A07 for ; Tue, 3 Jul 2012 06:29:29 -0300 (ADT) Received: from mail-bk0-f46.google.com ([209.85.214.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SlzQF-0002pz-PO for pgsql-performance@postgresql.org; Tue, 03 Jul 2012 09:29:29 +0000 Received: by bkwj10 with SMTP id j10so1400712bkw.19 for ; Tue, 03 Jul 2012 02:29:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject :content-type; bh=YPMY0tFo8DnT1Z6I170Wk7CuJuciN6OQgYpEkfFlZDY=; b=T7t05jLrmVneDRFsLXcXYiucHXxxYWky3ZFHnjVkg1s57BDcIbjTwIX0lcMvBzqsJh iUt+pQkzHW7wuvmiUoT3Hsbb1EVXd1YKvacZkzL1sFXfeODjxuc7TO6zpkUzkix468zi GzYTmGh+P78w5oNuSFWcRPYz1vCm/v/4W+pz4KN8Ayxj6X49AxuNIfva1yG2E4xYfaV0 VpbiFFP5NSRm/11yiB+mmAv5J75wPnMUBAckUdrJgfu1DyLfRcExHHTFH4Xrym8r+tVM SAbU9k16OABnE7m/cTjxyGPoqN0G43tZJGGZQmEPBpRl8imH8pBnl7UjW78NtzNMhW8B 1Y+A== Received: by 10.204.128.201 with SMTP id l9mr8967720bks.68.1341307752828; Tue, 03 Jul 2012 02:29:12 -0700 (PDT) Received: from athlon.ww1.0pc.eu (66.Red-88-29-2.staticIP.rima-tde.net. [88.29.2.66]) by mx.google.com with ESMTPS id h18sm16694674bkh.8.2012.07.03.02.29.11 (version=SSLv3 cipher=OTHER); Tue, 03 Jul 2012 02:29:12 -0700 (PDT) Message-ID: <4FF2BB65.7080903@gmail.com> Date: Tue, 03 Jul 2012 11:29:09 +0200 From: PV User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:12.0) Gecko/20120430 Thunderbird/12.0.1 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: static virtual columns as result? Content-Type: multipart/alternative; boundary="------------070102040101040209080507" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201207/4 X-Sequence-Number: 47185 This is a multi-part message in MIME format. --------------070102040101040209080507 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hello, I "have" this two tables: Table Cat: id|A|B --+-+- 1|3|5 2|5|8 3|6|9 Table Pr: id|Catid| --+- 1|3 2|2 3|1 I need replace "Catid" column for corresponding values A and B (Table Cat) in Table Pr. Virtual table like this: Table Pr: id|Catid|A|B --+-+-+ 1|3|6|9 2|2|5|8 3|1|3|5 Something like this, but that works,... SELECT * FROM pr WHERE pr.a /*> 1 AND*/*//* pr.b < 10; With subqueries is too slow: SELECT * FROM "Pr" AS p, (SELECT "id" AS cid FROM "Cat" WHERE "lft" > 1 AND "rgt" < 10) AS c WHERE p."Cat"=c."cid" AND (...)) ORDER BY "Catid" ASC OFFSET 0 LIMIT 40 Any suggestion? --------------070102040101040209080507 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hello,

I "have" this two tables:

Table Cat:
id|A|B
--+-+-
1|3|5
2|5|8
3|6|9

Table Pr:
id|Catid|
--+-
1|3
2|2
3|1

I need replace "Catid" column for corresponding values A and B (Table Cat) in Table Pr.

Virtual table like this:
Table Pr:
id|Catid|A|B
--+-+-+
1|3|6|9
2|2|5|8
3|1|3|5

Something like this, but that works,...

SELECT * FROM pr WHERE pr.a > 1 AND pr.b < 10;

With subqueries is too slow:
SELECT * FROM "Pr" AS p, (SELECT "id" AS cid FROM "Cat" WHERE "lft" > 1 AND "rgt" < 10) AS c WHERE p."Cat"=c."cid" AND (...)) ORDER BY "Catid" ASC OFFSET 0 LIMIT 40

Any suggestion?

--------------070102040101040209080507--