From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Net Virtual Mailing Lists <mailinglists(at)net-virtual(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Creating index on a view? |
Date: | 2004-11-24 13:24:43 |
Message-ID: | 41A48B9B.3010908@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Net Virtual Mailing Lists wrote:
>
> CREATE TABLE table2 (
> table2_id INTEGER,
> table2_desc VARCHAR,
> table3_id INTEGER[]
> );
>
> CREATE TABLE table3 (
> table3_id INTEGER,
> table3_desc VARCHAR
> );
>
>
> What I need is an "indirect index" (for lack of a better phrase) that
> allows me to do:
>
> SELECT b.table3_id, b.table3_desc FROM table2 a, table 3 b WHERE
> a.table2_id = 4 AND b.table3_id = ANY (a.table3_id);
>
> .. in the above example, the "4" is the variable component in the
> query... THe table3_id in table2 has the value of '{7}' - so when I do
> the above select, it is actually retrieving records from table3 where
> table3_id is equal to 7.
[snip]
> SELECT b.table3_id, b.table3_desc FROM table3 b WHERE b.table3_id = 7;
I don't think you want to use an array here. If you were to split your
tables:
table2 (t2_id, t2_desc);
table3 (t3_id, t3_desc);
table2_and_3 (t2_id, t3_id);
Then, you should find everything a lot easier.
Try not to use arrays as a set.
--
Richard Huxton
Archonet Ltd
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2004-11-24 13:26:51 | Re: Moving/Using Postgres Binaries on multiple machines |
Previous Message | Neil Conway | 2004-11-24 13:06:38 | Re: Can't get planner to use multicolumn index on large |