how to implement cursor functionality in vertica

i want to loop through all the tables for a schema and do some operation on them like using a CTE to find the the count of duplicates in each table .

Answers

  • i have also used while loop and a temporary table in SQL Server,but loops are also not supported in vertica

  • marcothesanemarcothesane - Select Field - Administrator

    You're faster when you avoid loops. Use SQL generating SQL. Like so, to count all rows in all tables in schema published:

    SELECT 
      'SELECT '''
    ||table_schema||'''||''.''||'''||table_name||''' AS obj'
    ||',COUNT(*) AS rowcount FROM '
    ||table_schema||'.'||table_name||';'
    FROM tables
    WHERE table_schema='published';
                                                                    ?column?
    ------------------------------------------------------------------------------------------------------------------------------------------
     SELECT 'published'||'.'||'MeasurementWithHistory_published' AS obj,COUNT(*) AS rowcount FROM published.MeasurementWithHistory_published;
     SELECT 'published'||'.'||'Installation_published' AS obj,COUNT(*) AS rowcount FROM published.Installation_published;
     SELECT 'published'||'.'||'ConsumerSession_published' AS obj,COUNT(*) AS rowcount FROM published.ConsumerSession_published;
     SELECT 'published'||'.'||'Item_published' AS obj,COUNT(*) AS rowcount FROM published.Item_published;
     SELECT 'published'||'.'||'item_Test' AS obj,COUNT(*) AS rowcount FROM published.item_Test;
    

    The output you get, you can put into a file, and execute that file as a script.

  • edited May 2021

    Using SQL will save time and resources; using loops will not give such a result. In urgent need, I use the service https://cashloansnearby.com/texas/lewisville/ to find a cash loan to pay for important services.

  • In all cases, you can still use any programming language and connect to Vertica. You can then use "loops" if you want to.

Leave a Comment

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