Hello all,
I have a lookup table consisting of 100,000+ rows.
99% of the lookup values resolve to 'Unknown'.
Building the lookup table takes a long time.
I would like to remove the 'Unknown' entries from the
table and provide a VIEW to emulate them.
The VIEW would need to provide all 100,000+ rows by
using the reduced lookup data and generating the
remaining values on the fly.
The lookup table structure:
CREATE TABLE lookup_data (
id1 INTEGER,id2 INTEGER,name TEXT,
PRIMARY KEY (id1, id2)
);
id1 is an INTEGER; from 0 through to 50,000+
id2 is an INTEGER; either 9 or 16.
Example data:
INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a');
INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b');
INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c');
INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd');
INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e');
INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f');
INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g');
INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h');
INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i');
INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j');
INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k');
INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l');
..
In the example data, entries where id1 is 5,6,7,9 are 'Unknown';
The VIEW would return:
id1, id2, name
1, 9, 'a'
1, 16, 'b'
2, 9, 'c'
2, 16, 'd'
3, 9, 'e'
3, 16, 'f'
4, 9, 'g'
4, 16, 'h'
5, 9, 'Unknown'
5, 16, 'Unknown'
6, 9, 'Unknown'
6, 16, 'Unknown'
7, 9, 'Unknown'
7, 16, 'Unknown'
8, 9, 'i'
8, 16, 'j'
9, 9, 'Unknown'
9, 16, 'Unknown'
10, 9, 'k'
10, 16, 'l'
I am using Postgres 7.2.1, which prevents me using a
function to return a result set.
Can I achieve this in pure SQL?
Many thanks,
JJ Gabor.