We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


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

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

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 - Select Field - Administrator

    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 - Select Field - Administrator

    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