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

TimTim Registered User

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

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file