From: "Jonathan M. Gardner" <jgardner@jonathangardner.net>
To: JJ Gabor <jj.gabor@ntlworld.com>
Subject: Re: VIEW on lookup table
Date: Fri, 5 Mar 2004 08:39:12 -0800
User-Agent: KMail/1.6
Cc: pgsql-sql@postgresql.org
References: <20040227221844.GA937@smak>
In-Reply-To: <20040227221844.GA937@smak>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: Text/Plain;
  charset="windows-1252"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200403050839.15130.jgardner@jonathangardner.net>
Received-SPF: pass (dervish.jonathangardner.net: sender at 66.92.192.166 was
	authenticated)

-----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=
=20
join that with lookup_data, using a "left outer join", and then use a=20
case statement for the value -- when NULL, 'Unknown', then it should=20
work.

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

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

- --=20
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W
YiJY3ZYsAXNfjjBTCF0vGKE=3D
=3D5EIl
-----END PGP SIGNATURE-----