how to implement cursor functionality in vertica

luvpurohit

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 .

luvpurohit

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

marcothesane

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.

BettyJones

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.

badrouali

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