Re: [GENERAL] Creating index on a view?

[Richard Huxton]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend