Date: Sun, 7 Mar 2004 13:40:43 +0000
From: JJ Gabor <jj.gabor@ntlworld.com>
To: pgsql-sql@postgresql.org
Subject: Re: VIEW on lookup table
Message-ID: <20040307134043.GA663@smak>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <20040227221844.GA937@smak>
	<200403050839.15130.jgardner@jonathangardner.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200403050839.15130.jgardner@jonathangardner.net>
User-Agent: Mutt/1.5.5.1+cvs20040105i


> Yes. If you create a table with all of the values, 1 to 100,000+, and then 
> join that with lookup_data, using a "left outer join", and then use a 
> case statement for the value -- when NULL, 'Unknown', then it should 
> work.

This would still require constructing a large table, which is what I
want to avoid.

> I would look at bending the requirements a bit before I do this. Why do 
> you want the string "Unknown" and not NULL? What is this table  going to 
> be used for? Also, just because you can't write a function in the 
> database to do this doesn't mean you can't write a function in perl or 
> python outside of the database to do it.

The technology used to access the database does not cope very well
with NULL/missing rows/colunns :/

As it turns out, the lookup table has been ditched.

> Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really 
> shouldn't be used anymore.

Mutch as I would like to, this is not an option.

Thanks for your help,
JJ


On Fri, Mar 05, 2004 at 08:39:12AM -0800, Jonathan M. Gardner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Friday 27 February 2004 2:18 pm, JJ Gabor wrote:
> > 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?
> 
> Yes. If you create a table with all of the values, 1 to 100,000+, and then 
> join that with lookup_data, using a "left outer join", and then use a 
> case statement for the value -- when NULL, 'Unknown', then it should 
> work.
> 
> I would look at bending the requirements a bit before I do this. Why do 
> you want the string "Unknown" and not NULL? What is this table  going to 
> be used for? Also, just because you can't write a function in the 
> database to do this doesn't mean you can't write a function in perl or 
> python outside of the database to do it.
> 
> Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really 
> shouldn't be used anymore.
> 
> - -- 
> Jonathan Gardner
> jgardner@jonathangardner.net
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.3 (GNU/Linux)
> 
> iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W
> YiJY3ZYsAXNfjjBTCF0vGKE=
> =5EIl
> -----END PGP SIGNATURE-----