Vertica for loop to iterate over resultset

jumanji

I want to loop over a set of rows and call user defined function to perform some operation on it

Say I have a table below, with hash value stored in the hash column of the table

Desc temp
————————————
id Number
name Varchar
hash VarBinary

Now I have a user defined function usr_fun which takes two hash values, first row hash value and next row hash value and performs some operation on them and then returns the new hash value as the result. Now it should use this result and pickup the third row hash value and execute the same funtion and continue like this for all the rows and finally return the result.

Select usr_fun(row.hash,nextrow.hash) as result from temp

Result
—————
XXXXXXXX

Same problem visually

select * from Temp
—————
Hash
—————
1
2
3
4

Select usr_fun(row.hash,nextrow.hash) as result from temp
(1+2=3, 3+3=6, 6+4=10)
Result
—————
10

Thanks

marcothesane

Hi jumanji

This seems so trivial to me that I assume I got something wrong.

But, from what you seem to be after,

• you're creating your own hash value when Vertica has a hash function returning int
• you're planning to write a user function to get a running sum.

The example below does all just in SQL - and it's not even Vertica specific
1. check the input values in the input Common Table expression in the WITH clause, including the hash value you're suggesting - of course, I have an ID to sort by later, as tables are never sorted.
2. check with_vertica_hash, where I add a HASH() function on the first two columns of the input in-line table
3. check the SUM(jumanji_hash) function call in the final SELECT, which behaves pretty much like you're expecting your function to behave.

This is ANSI-1999 SQL. Not even Vertica specific. If I'm right, SQL seems to become more and more one of the best kept secrets as to its versatility and support for productiveness ...

Or did I get something wrong?

Happy playing - Marco

    WITH
    input(id,_name,jumanji_hash) AS(
              SELECT 1,'Arthur Dent'      ,1
    UNION ALL SELECT 2,'Ford Prefect'     ,2
    UNION ALL SELECT 3,'Zaphod Beeblebrox',3
    UNION ALL SELECT 4,'Tricia McMillan'  ,4
    )
    ,
    with_vertica_hash AS(
    SELECT
     *
    ,HASH(id,_name) AS vertica_hash
    FROM input
    )
    SELECT
      *
    , SUM(jumanji_hash) OVER(
       ORDER BY id
      ) AS result
    FROM with_vertica_hash;
     id |       _name       | jumanji_hash |    vertica_hash     | result
    ----+-------------------+--------------+---------------------+--------
      1 | Arthur Dent       |            1 |  418582588360786296 |      1
      2 | Ford Prefect      |            2 | 7701546877505754318 |      3
      3 | Zaphod Beeblebrox |            3 | 7687242525272749043 |      6
      4 | Tricia McMillan   |            4 | 8286721946822845776 |     10

jumanji

Hi Marcothesane,

Thanks Much!. On the similar lines in the actual problem. I have a SUM(jumanji_hash) which takes two arguments. I can't modify this function,so, I need to modify this query such that SUM(jumanji_hash,result) and finally return 10.

The function what I have only works on the two arguments that I pass and doesn't have access to rowset implicitly.

To put it in simpler words. how can we achieve the same output with

int user_sum(jumanji_hash,result) { return jumanji_hash+result;} it doesn't have access to the rows or any other informartion.