From: JJ Gabor <jj.gabor@ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: VIEW on lookup table
Date: Fri, 27 Feb 2004 22:18:44 +0000
Message-ID: <20040227221844.GA937@smak> (raw)
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.
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: jj.gabor@ntlworld.com
Subject: Re: VIEW on lookup table
In-Reply-To: <20040227221844.GA937@smak>
* 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