The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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 .


  • luvpurohitluvpurohit Vertica Customer

    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 '''
    ||table_schema||'''||''.''||'''||table_name||''' AS obj'
    ||',COUNT(*) AS rowcount FROM '
    FROM tables
    WHERE table_schema='published';
     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.

  • BettyJonesBettyJones Community Edition User
    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 to find a cash loan to pay for important services.

  • badroualibadrouali Vertica Employee

    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
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file