Re: VIEW on lookup table

-----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(at)jonathangardner(dot)net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W
YiJY3ZYsAXNfjjBTCF0vGKE=
=5EIl
-----END PGP SIGNATURE-----