Skip site navigation (1) Skip section navigation (2)

VIEW on lookup table

From: JJ Gabor <jj(dot)gabor(at)ntlworld(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: VIEW on lookup table
Date: 2004-02-27 22:18:44
Message-ID: 20040227221844.GA937@smak (view raw or flat)
Thread:
Lists: pgsql-sql
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.










Responses

pgsql-sql by date

Next:From: Bruce MomjianDate: 2004-02-28 16:18:53
Subject: Re: User defined types -- Social Security number...
Previous:From: Tom LaneDate: 2004-02-27 18:12:17
Subject: Re: Convert INT to INTERVAL?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group