User defined function to look up value of a table column using a value in another column

Tim

Hi,
I would like to write a function that would return the value of a particular column from a table using the value of another column as input.
Here are some rows from my table:

Key Value
1 Chicago
2 New York
3 Los Angeles

I want the function to return Chicago if I call myfunction(1), New York if I call myfunction(2) and so on ...
Can someone please guide me how to write such a function? I am using Vertica 8.0.1-3

Thanks,
Thomas

Jim_Knicely

What is the use case for your function?

Why not simply use a sub-select as a column or use a table join?

Examples:

dbadmin=> SELECT * FROM cities;
 pk |    city
----+-------------
  1 | Chicago
  2 | New York
  3 | Los Angeles
(3 rows)

dbadmin=> SELECT * FROM some_fact;
 fact_pk | city_pk
---------+---------
       1 |       1
       2 |       3
(2 rows)

dbadmin=> SELECT (SELECT city FROM cities WHERE pk = 1), (SELECT city FROM cities WHERE pk = 2), (SELECT city FROM cities WHERE pk = 3);
 ?column? | ?column? |  ?column?
----------+----------+-------------
 Chicago  | New York | Los Angeles
(1 row)

dbadmin=> SELECT fact_pk, (SELECT city FROM cities WHERE pk = city_pk) city FROM some_fact;
 fact_pk |    city
---------+-------------
       1 | Chicago
       2 | Los Angeles
(2 rows)

dbadmin=> SELECT fact_pk, city FROM some_fact JOIN cities ON pk = city_pk;
 fact_pk |    city
---------+-------------
       1 | Chicago
       2 | Los Angeles
(2 rows)

If you still want the function you could create a User Defined SQL Function:

dbadmin=> CREATE FUNCTION myfunction(x INT) RETURN VARCHAR
dbadmin-> AS
dbadmin-> BEGIN
dbadmin->   RETURN CASE x
dbadmin->            WHEN 1 THEN 'Chicago'
dbadmin->            WHEN 2 THEN 'New York'
dbadmin->            WHEN 3 THEN 'Los Angeles'
dbadmin->          END;
dbadmin-> END;
CREATE FUNCTION

dbadmin=> SELECT myfunction(1), myfunction(2), myfunction(3);
 myfunction | myfunction | myfunction
------------+------------+-------------
 Chicago    | New York   | Los Angeles
(1 row)

Instead of maintaining the lookup data (i.e. cities) in a table, you have to maintain the function.

Note that a table join would perform much better than a function call.

Jim_Knicely

If you upgrade to Vertica 8.1, the "Flattened Tables" feature might help you depending on your use case...

Example:

dbadmin=> SELECT * FROM cities;
 pk |    city
----+-------------
  1 | Chicago
  2 | New York
  3 | Los Angeles
(3 rows)

dbadmin=> CREATE TABLE some_flat_fact_table (fact_pk INT PRIMARY KEY NOT NULL, city_pk INT REFERENCES cities(pk), city VARCHAR DEFAULT (SELECT city FROM cities WHERE cities.pk = some_flat_fact_table.city_pk));
CREATE TABLE

dbadmin=> INSERT INTO some_flat_fact_table (fact_pk, city_pk) SELECT 1, 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO some_flat_fact_table (fact_pk, city_pk) SELECT 1, 3;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM some_flat_fact_table;
 fact_pk | city_pk |    city
---------+---------+-------------
       1 |       1 | Chicago
       1 |       3 | Los Angeles
(2 rows)